Excerpt 

This section provides an overview of how to perform mathematical operations between columns. 
Check Data Types
Before you begin, you should verify that the data types of the two columns match. Check the icon in the upper left of each column to verify that they match.
To change the data type, you can:
 Click the data type icon.
 Select Edit data type from the column menu.
Check Values
After setting data types, you should address any missing or mismatched values in the column. For example, if you change a column's data type from Decimal to Integer, values that contain decimal points may be reported as mismatched values. Use the ROUND
function to round them to the nearest integer.
D trans 

Type  step 

p01Name  Columns 

p01Value  myColumn 

p02Name  Formula 

p02Value  ROUND(myColumn) 

SearchTerm  Edit column with formula 


Tip 

Tip: You can use the FLOOR or CEILING functions to force rounding down or up to the nearest integer. 
Syntax of Math Functions
You can express mathematical operations using numeric operators or function references. The following two examples perform the same operation, creating a third column that sums the first two.
Numeric Operators:
D trans 

p03Value  'colD' 

Type  step 

p01Name  Formula type 

p01Value  Single row formula 

p02Name  Formula 

p02Value  (colA + colB + colC) 

p03Name  New column name 

SearchTerm  New formula 


Math Functions:
D trans 

p03Value  'colD' 

Type  step 

p01Name  Formula type 

p01Value  Single row formula 

p02Name  Formula 

p02Value  ADD(colA,colB) 

p03Name  New column name 

SearchTerm  New formula 


Info 

NOTE: Expressions containing numeric operators can contain more than two column references or values, as well as nested expressions. Math functions support two references only. 
Add One Column into Another
To perform math operations, you can use the Edit column with formula transformation to update values in a column based on a math operation. The following transformation multiplies the column by 10 and adds the value of colB
:
D trans 

Type  step 

p01Name  Columns 

p01Value  colA 

p02Name  Formula 

p02Value  ((colA * 10) + colB) 

SearchTerm  Edit column with formula 


All values in colA
are modified based on this operation.
Add Selective Values from One Column into Another
You can use the Edit column with formula transformation to perform math operations based on a condition you define. In the following step, the Cost
column is replaced reduced by 10% if the Qty
column is more than 100. The expression is rounded down to the nearest integer, so that the type of the column (Integer) is not changed:
D trans 

Type  step 

p01Name  Columns 

p01Value  Cost 

p02Name  Formula 

p02Value  IF(Qty > 100, ROUND(Cost * 0.9), Cost) 

SearchTerm  Edit column with formula 


For rows in which Qty
is less than 100, the value of Cost
is written back to the column (no change).
Add Two Columns into a New Third Column
To create a new column in which a math operation is performed on two other columns, use the New Formula transformation. The following multiplies Qty
and UnitPrice to yield Cost:
D trans 

p03Value  'Cost' 

Type  step 

p01Name  Formula type 

p01Value  Single row formula 

p02Name  Formula 

p02Value  MULTIPLY(Qty,UnitPrice) 

p03Name  New column name 

SearchTerm  New formula 


Working with More than Two Columns
If you need to work with more than two columns, numeric operators allow you to reference any number of columns and static values in a single expression.
However, you should be careful to avoid making expressions that are too complex, as they can be difficult to parse and debug.
Tip 

Tip: When performing complex mathematic operations, you may want to create a new column to contain the innermost computations of your expression. Then, you can reference this column in the subsequent step, which generates the full expression. In this manner, you can build complex equations in a way that is easier to understand for other users of the recipe. The final step is to delete the generated column. 
Concatenating Columns
If you are concatenating stringbased content between multiple columns, use the Merge Columns transformation. In the following example, the Merge Columns transformation is used to bring together the order ID (ordId
) and product ID (prodId
) columns, with the dash character used as the delimiter between the two column values:
D trans 

RawWrangle  true 

p03Value  primaryKey 

Type  step 

WrangleText  ##NO_WRANGLE## 

p01Name  Columns 

p01Value  ordId, prodId 

p02Name  Separator 

p02Value  '' 

p03Name  New column name 

SearchTerm  Merge columns 


Tip 

Tip: This method can be used for columns of virtually any type. Change the data type of each column to String and then perform the merge operation. 
Array column types can be concatenated with the ARRAYCONCAT function.
Tip 

Tip: You can also use the MERGE function to accomplish the above actions. The function method is useful if you are performing a separate transformation action on the data involved. For example, you could use the function if you are using the Edit formula column to modify a column in place. 
Summing Rows
You can use aggregate functions to perform mathematic operations on sets of rows. Aggregated rows are collapsed and grouped based on the functions that you apply to them.
D s also 

inCQL  true 

label  (label = "enrichment_tasks") 

