Page tree

 

Contents:


You can create a new column by adding or editing a formula on any existing column by performing the following steps:

New Formula

The New Formula transformation allows you to create a new column based upon a formula that you provide to the transformation. Below are some examples.

Add a column of text values

You can insert a new column containing a string value that you specify as part of the transformation. In the following example, the status column is created, and all values in it are set to ok.

Transformation Name New formula
Parameter: Formula type Single row formula
Parameter: Formula 'ok'
Parameter: New column name status

Add a column that uses a function

You can insert a new column by using a function. In the following example,  the currentyear column is extracted as a new column from the TransactionDate column using YEAR function. For more information on extracting date information, see Extract Values.

Transformation Name New formula
Parameter: Formula type Single row formula
Parameter: Formula YEAR (TransactionDate)
Parameter: New column name currentyear

Add a column that references another column

You can also insert columns containing references to other columns. In the following example, the totalCost column is created called totalCost, which is based on the formula using three separate columns: baseCosttotalTaxtotalDiscount

Transformation Name New formula
Parameter: Formula type Single row formula
Parameter: Formula baseCost + totalTax - totalDiscount
Parameter: New column name totalCost

Add a column using constants, functions, and column references

You can insert a column by using nested expressions by using constants, functions, and column references. In the following example, the Three column is created, which is based on nested functions  ROUND and DIVIDE.

Transformation Name New formula
Parameter: Formula type Single row formula
Parameter: Formula ROUND(DIVIDE(10,3),0)
Parameter: New column name Three

Merge Columns

You can merge two or more columns together to create a new column containing the merged values. For more information, see Add Two Columns.

Extract Values from a Column

You can extract values based on patterns or literal values from one column and insert them into a new column. See Extract Values.

Split Column Values

You can split the values in a column into separate columns based on delimiters and other conditions that you define. See Split Column.

Convert a Column into Multiple Columns

Unnest

You can extract values stored in an array into separate columns in your dataset. This type of transformation can be useful for unpacking nested data such as JSON into tabular format.

This page has no comments.