=20

=20

This section provides an overview of how to perform mathematical ope=
rations between columns.
**Contents:**

=20
=20

=20
=20
=20

Before you begin, you should verify that the data types of the two colum= ns match. Check the icon in the upper left of each column to verify that th= ey match.

To change the data type, you can:

- Click the data type icon.
- Select
**Edit data type**from the column menu. =

After setting data types, you should address any missing or mismatched v=
alues in the column. For example, if you change a column's data type from D=
ecimal to Integer, values that contain decimal points may be reported as mi=
smatched values. Use the `ROUND`

function to round them to =
the nearest integer.

=20

=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20

=20
Transformation Name | `Edit column with formula` |
---|---|

Parameter: Columns | `myColumn` |

Parameter: Formula | `ROUND(myColumn)` |

**Tip: **You can use the `FLOOR`

or ```
CEILIN=
G
```

functions to force rounding down or up to the nearest integer.

You can express mathematical operations using numeric operators or funct= ion references. The following two examples perform the same operation, crea= ting a third column that sums the first two.

**Numeric Operators: **

=20

=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20

=20
Transformation Name | <= code>New formula |
---|---|

Parameter: Formula type | `Single row formula` |

Parameter: Formula | `(colA + colB + colC)` |

Parameter: New column name | `'colD'` |

**Math Functions: **

=20

=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20

=20
Transformation Name | <= code>New formula |
---|---|

Parameter: Formula type | `Single row formula` |

Parameter: Formula | `ADD(colA,colB)` |

Parameter: New column name | `'colD'` |

**NOTE: **Expressions containing numeric operators can cont=
ain more than two column references or values, as well as nested expression=
s. Math functions support two references only.

To perform math operations, you can use the Edit column with formula tra=
nsformation to update values in a column based on a math operation. Th=
e following transformation multiplies the column by 10 and adds the value o=
f `colB`

:

=20

=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20

=20
Transformation Name | `Edit column with formula` |
---|---|

Parameter: Columns | `colA` |

Parameter: Formula | `((colA * 10) + colB)` |

All values in `colA`

are modified based on this operatio=
n.

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<=
/code> column is more than 100. The expression is rounded down to the neare=
st integer, so that the type of the column (Integer) is not changed:
```

```
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
```
Transformation Name `Edit column with formula`

Parameter: Columns `Cost`

Parameter: Formula ```
IF(Qty > 100, ROUND(Cost * 0.9), Cost)<=
/code>
```

=20

`For rows in which `

is written back to the column (no change). `Qty`

is less than 100, the value of <=
code>Cost

To create a new column in which a math operation is performed on two oth=
er columns, use the New Formula transformation. The following multiplies&nb=
sp;`Qty`

and `UnitPrice to yield Cost:`

=20

=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20

=20
Transformation Name | <= code>New formula |
---|---|

Parameter: Formula type | `Single row formula` |

Parameter: Formula | `MULTIPLY(Qty,UnitPrice)` |

Parameter: New column name | `'Cost'` |

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 expres= sion.

However, you should be careful to avoid making expressions that are too = complex, as they can be difficult to parse and debug.

**Tip: **When performing complex mathematic operations, you=
may want to create a new column to contain the innermost computations of y=
our 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 rec=
ipe. The final step is to delete the generated column.

If you are concatenating string-based content between multiple columns, =
use the Merge Columns transformation. In the following example, the Me=
rge Columns transformation is used to bring together the order ID=
(`ordId`

) and product ID (

=20

=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20

=20
Transformation Name | `Merge columns` |
---|---|

Parameter: Columns | `ordId, prodId` |

Parameter: Separator | `'-'` |

Parameter: New column name | `primaryKey` |

**Tip: **This method can be used for columns of virtually a=
ny type. Change the data type of each column to String and then perform the=
merge operation.

Array column types can be concatenated w= ith the ARRAYCONCAT function.

**Tip: **You can also use the MERGE function to accomplish =
the above actions. The function method is useful if you are performing a se=
parate transformation action on the data involved. For example, you could u=
se the function if you are using the Edit formula column to modify a column=
in place.

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.

=20