User Tools

Site Tools


transformations:addcolumn

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision
Previous revision
transformations:addcolumn [2015/04/10 19:25] dmitrytransformations:addcolumn [2024/02/05 14:20] (current) craigt
Line 1: Line 1:
-===== Calculate new column(s) =====+{{ transformations:AddColumnsAction.png}} 
 +====== CALCULATE NEW COLUMN(S) =====
 +Category: Transform / Basic\\
  
-This transformation calculates one or more new columns using [[syntax:start|expression(s)]]. One expression creates one column.+\\  
 +=====Description===== 
 +This action calculates one or more new columns using [[syntax:start|expression(s)]]. One expression creates one column.
  
-Expression is calculated for every row. Other column values in the same row can be referred by column names in square brackets. It's not possible to refer from one row to another row or to another table. 
  
-EXAMPLE+\\ 
 +=====Use cases===== 
 +  *Creating a calculated result from numeric values in two (or more) other columns. 
 +  *Concatenating text values from two (or more) other columns. 
 +  *Creating a full-column list of the same text or numeric value. 
 + 
 + 
 +\\  
 +=====Action settings===== 
 +^Setting^Description^ 
 +|New column name|Enter the name of the new column.| 
 +|Expression or Value|Options:  //It's an expression// or //It's a text or number// Enter the expression used to calculate the new column values,\\ or, enter the text or numeric value that will be repeated for all values.| 
 +|Position|Choose where the new column(s) will appear in the dataset.  Options:  //Rightmost//, //Leftmost//, or //After column//\\ (and select the column the new column(s) will appear after).| 
 +|If column already exists|Choose how EasyMorph reacts if the new column name already exists in the dataset.\\  Options: //Rename new column// (a sequential number is appended to the column name), //Do not create new column//\\ (the column is not created and the workflow continues), or //Fail// (the action displays an error and the workflow stops).| 
 + 
 +\\  
 +=====Remarks===== 
 +The expression is calculated for every row. Other column values in the same row can be referred to by column names in square brackets. It's not possible to refer to other rows or tables. 
 + 
 +Instead of an expression, this action can insert a full column of static text or numeric values.\\ 
 + 
 +Subsequent expressions can reference columns previously created within the same //Calculate new column(s)// action.  (See example, below) 
 + 
 + 
 +\\  
 +=====Examples===== 
 + 
 +**Example #1:** Calculate the $ amount of compulsory deductions for each country
  
 **Source table:** Average income and deductions **Source table:** Average income and deductions
  
- Country  Gross income    Disposable income  ^  Compulsory deductions +^Country  ^Gross income   ^Disposable income  ^Compulsory deductions 
-| United States  |  56067  |  45582  |  18.7%  | +| United States  |  56067|  45582|  18.7%| 
-| Australia  |  51050  |  42617  |  16.52% +| Australia  |  51050|  42617|  16.52%| 
-| Switzerland  |  53716  |  48414  |  9.87%  | +| Switzerland  |  53716|  48414|  9.87%| 
-| Canada  |  45896  |  37469  |  18.36%  |+| Canada  |  45896|  37469|  18.36%| 
 +\\  
 +**Action parameters:** 
 +> New column name: Deductions $ 
 +> Expression: [Gross income] - [Disposable income
 + 
 +\\  
 +**Result table:** 
 + 
 +^Country  ^Gross income   ^Disposable income  ^Compulsory deductions  ^Deductions $  ^ 
 +| United States  |  56067|  45582|  18.7% 10485
 +| Australia  |  51050|  42617|  16.52% 8433
 +| Switzerland  |  53716|  48414|  9.87% 5302
 +| Canada  |  45896|  37469|  18.36% 8427| 
 + 
 +\\ \\ \\  
 +**Example #2:** Create multiple new columns within the same action. 
 + 
 +\\  
 +**Action parameters:** 
 +> New column #1: MyDate = today() 
 +> New column #2: MyYear = year([MyDate]) //(References the [MyDate] column previously created in this action.)// 
  
-**Objective:** calculate the $ amount of compulsory deductions for each country 
  
-**Transformation parameters:** +\\  
-  * New column nameDeductions $ +====Community examples==== 
-  * Expression: [Gross income] - [Disposable income]+  [[https://community.easymorph.com/t//1273/6|Split Excel spreadsheets into multiple spreadsheets]] ([[https://community.easymorph.com/uploads/short-url/6iWYdZJc4yAvqYMbTGZWXtVFmvL.morph|Project]]; Module: //Main//; Group: //Tab 1//; Table: //Spreadsheet A.xlsx//;\\ Action position: //3//) 
 +  [[https://community.easymorph.com/t//1283/2|How to split Number and Alphabetical Characters from a Cell]] ([[https://community.easymorph.com/uploads/short-url/wzaamfKXHrZWs690Lc5akE2OWsV.morph|Project]]; Module: //Main//; Group: //Tab 1//; Table: //Table 1//; Action position: //2//) 
 +  [[https://community.easymorph.com/t//1489/1|How to calculate YoY change using self-join]] ([[https://community.easymorph.com/uploads/short-url/n3koKAS8IXLsc5Oa9647ELLijkp.morph|Project]]; Module: //Main//; Group: //Self-join//; Table: //This year//; Action position: //4//) 
 +  [[https://community.easymorph.com/t//1587/7|How to format the duration in more than 24h]] ([[https://community.easymorph.com/uploads/short-url/w6CnxwiidYjcJHNRrmUIXI74NC2.morph|Project]]; Module: //Main//; Group: //Tab 1//; Table: //Table 1//; Action position: //6//) 
 +  * [[https://community.easymorph.com/t//1919/2|Split after last space]] ([[https://community.easymorph.com/uploads/short-url/obnKd1zEnmJ9NkAEx53ho7sz9Gw.morph|Project]]; Module: //Main//; Group: //Tab 1//; Table: //Table 1//; Action position: //2//) 
 +  * [[https://community.easymorph.com/t//2060/2|Search, remove, and shift]] ([[https://community.easymorph.com/uploads/short-url/cCqOTak9SLKPXbityR5IAlgULJa.morph|Project]]; Module: //Main//; Group: //Regrouping//; Table: //ExtractingData.xlsx//; Action position: //5//) 
 +  * [[https://community.easymorph.com/t//2488/2|Split text into columns problem split when data changes from text to numbers]] ([[https://community.easymorph.com/uploads/short-url/fKwR2v9mZiSQ6cXclMb446ittht.morph|Project]]; Module: //Main//; Group: //Group 1//;\\ Table: //Variant 1//; Action position: //2//)
  
-**Output table:**+\\  
 +=====Shortcuts===== 
 +Fast ways to create the action:\\ 
 +  *Right-click on a column heading, and select "Insert column(s)"
 +  *Click the drop-down arrow in a column heading and select "Insert column(s)"
 +In either case, a new column with a default column name will appear //after// the selected column.
  
-^  Country  ^  Gross income    Disposable income  ^  Compulsory deductions  ^  Deductions $  ^ +\\  
-| United States  |  56067  |  45582  |  18.7%  |  10485  | +=====See also===== 
-| Australia  |  51050  |  42617  |  16.52%  |  8433  | +  * [[transformations:replacecolumns|Modify column(s)]] 
-| Switzerland  |  53716  |  48414  |  9.87%  |  5302  | +  * [[transformations:Peek|Peek]] 
-| Canada   45896  |  37469  |  18.36%  |  8427  | +  * [[transformations:rule|Rule]] 
- +  * [[transformations:subtotal|Total/Subtotals]]
  
-See also 
-  * [[transformations:replacecolumn|Calculate and replace column]] 
transformations/addcolumn.1428708304.txt.gz · Last modified: 2015/04/10 19:25 by dmitry

Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki