Date: Sat, 24 Jul 2021 02:37:36 +0000 (GMT) Message-ID: <1856412032.24770.1627094256046@6a789edf488b> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_24769_19067385.1627094256046" ------=_Part_24769_19067385.1627094256046 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 The following creates a third col= umn with a dash between the values of the two source columns:

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name `Merge columns` `ColA, ColB` `'-'` `ColC`
=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 with the ARRAYCONCAT function. Se= e ARRAYCONCAT Function.<= /p>

## 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_24769_19067385.1627094256046--