Date: Wed, 8 Dec 2021 11:50:53 +0000 (GMT) Message-ID: <1784184103.88031.1638964253444@9c5033e110b2> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_88030_1447990965.1638964253444" ------=_Part_88030_1447990965.1638964253444 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html Add Two Columns

Contents:

=20

=20

=20
=20

This section provides an overview of how to perform mathematical operati= ons between columns.

## Check Data Types

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. See&n= bsp;Column Menus.

## Check Values

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
Transformation Name `Edit column with formula` `myColumn` `ROUND(myColumn)`
=20

See ROUND Function.

Tip: You can use the `FLOOR` or ```CEILIN= G``` functions to force rounding down or up to the nearest integer.

See FLOOR Function.

See CEILING Function.

## Add One Column into Anothe= r

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
Transformation Name `Edit column with formula` `colA` `((colA * 10) + colB)`
=20

All values in `colA` are modified based on this operatio= n.

## Add Sel= ective 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<= /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 NameEdit column with formulaParameter: ColumnsCostParameter: FormulaIF(Qty > 100, ROUND(Cost * 0.9), Cost)<= /code> =20 ```

```For rows in which Qty is less than 100, the value of <= code>Cost``` is written back to the column (no change).

## Add Two Columns i= nto a New Third Column

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
Transformation Name <= code>New formula `Single row formula` `MULTIPLY(Qty,UnitPrice)` `'Cost'`
=20

## Working with More th= an 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 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.

## Concatenating Columns

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 (prodId) columns, with the dash ch= aracter used as the delimiter between the two column values:

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name `Merge columns` `ordId, prodId` `'-'` `primaryKey`
=20

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. See ARRAYCONCAT Function.

Tip: You can also use the `MERGE` function t= o accomplish the above actions. The function method is useful if you are pe= rforming a separate transformation action on the data involved. For example= , you could use the function if you are using the Edit formula column to mo= dify a column in place. See MERGE = Function.

## 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. See Aggregate Functions.

------=_Part_88030_1447990965.1638964253444--