Message-ID: <756258758.1767.1560745995640.JavaMail.daemon@e613c95ee270> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_1766_1433871538.1560745995640" ------=_Part_1766_1433871538.1560745995640 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
Transformation Name `Edit column with formula` `myColumn` `ROUND(myColumn)`

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 `set` transf= orm to update values in a column based on a math operation. The following t= ransform multiplies the column by 10 and adds the value of ```colB<= /code>:```

``` =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 Transformation NameEdit column with formulaParameter: ColumnscolAParameter: Formula((colA * 10) + colB) All values in colA are modified based on this operatio= n. Add Sel= ective Values from One Column into Another You can use the set transform to perform math operatio= ns 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, s= o 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 Transformation NameEdit column with formulaParameter: ColumnsCostParameter: FormulaIF(Qty > 100, ROUND(Cost * 0.9), Cost)<= /code> 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 Transformation Name<= code>New formulaParameter: Formula typeSingle row formulaParameter: FormulaMULTIPLY(Qty,UnitPrice)Parameter: New column name'Cost' 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 Transformation NameMerge columnsParameter: ColumnsColA, ColBParameter: Separator'-'Parameter: New column nameColC 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. 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_1766_1433871538.1560745995640-- ```