Using Calculated Columns

The below tutorial will cover what calculated columns are and how they can be used.

Calculated Columns are newly created columns that can be added to the row. These are a calculation based on the data from the current row.

They enable you to transform values using the in-built functions to create new row values.

They can be added to your schema map like any other column and mapped to your target.

Calculated columns are written into the Dynamic Columns base class, but provide a simpler way of writing simple expressions than Dynamic Columns.

Creating a Calculated Column

To add a new calculated column press the Fx button in the in the Data Source toolbar or use the shortcut keys ALT + C.

This opens the calculated column configuration window where you can give your column a name, determine a return data type and type in an expression to be used. The name must be unique within your schema.

The expression is the data result of the column for the given row. This is a C# expression however we have made this to be more like excel with the introduction of many functions for typical scenarios. Please see our documentation site for a list of available functions.

Editing a Calculated Column

To edit a calculated column, double click on the column to open the window. Then edit the details as required. Click OK to save your changes.

Deleting a Calculated Column

To delete a calculated column highlight the column you wish to remove and then go to the data source toolbar and click the Delete Calculated Column button.

Examples of Calculated Columns

Return

If you are requiring a column to be mapped to multiple fields in your target, you can use calculated columns to do this. Each column name in the schema map needs to be unique otherwise you will return errors.

To do achieve this in a calculated column you can simply type in a unique name for your column and then type in the column name you with to return in the expression field. This will then return the chosen column under the new column name.

Concatenate

For when you want to join two string values. e.g. First Name and Last Name into one column so that you have a customers full name in one field.

CONCATENATE(first_name," ",last_name)

Format

When you want each row of a column to follow a specified format. e.g. Generating usernames from a last name with the first letter of a forename all @companyA.com

FORMAT("{0}{1}@companyA.co.uk",last_name,(LEFT(first_name,1)))

Troubleshooting

Does not exist

If you receive an error like the following:

Check that the entity to are referencing is spelt correctly and does exist in your source data.

Duplicate Column

If you have a red exclamation mark appearing next to the column you are trying to map in your schema map then you have duplicated a column. Each column mapped in the schema map needs to have a unique name. If you are trying to map the same column twice or more then please refer to the 'Return Column' section above.

Wrong data type

If you receive an error like the following:

Function not recognised

If you are getting an error stating your function is not recognised or does not exist. Please check that the function you have typed is in capitals as calculated columns are case sensitive as they follow C# rules.

Cannot find Column/Function etc.

If you are receiving an error saying that the system cannot find your column or function etc. Please check your spelling and please check your capitalisation as calculated columns are case sensitive as they follow C# rules.