Calculated Column Examples
This page outlines how to use Calculated Columns.
Table of Contents |
|---|
<CalculatedColumn> can be defined in addition to the base set that come from the source. The formula can reference execution parameters. Examples 1 and 2 are shown first within the experience as well as their XML equivalent.
Example 3 demonstrates the use of Javascript in Calculated Columns.
The value of this column for each record is whatever the value is of the lob parameter.
XML equivalent of the above example:
<Parameters>
<Schema>
<CalculatedColumn name="lob" formula="@lob" dataType="Text" maxLength="100"
isMandatory="false" description="" trimWhitespace="true"/>
<CalculatedColumn name="name" formula="CONCAT(firstname, lastname)"
dataType="Text" maxLength="100" isMandatory="false"
description="" trimWhitespace="true"/>
</Schema>
The CONCAT function supports more than 2 parameters, and any literal values must be enclosed in single quotes (e.g. 'abc')
The values of two columns are concatenating together.
XML equivalent of the above example:
<Parameters>
<Schema>
<CalculatedColumn name="lob" formula="@lob" dataType="Text" maxLength="100"
isMandatory="false" description="" trimWhitespace="true"/>
<CalculatedColumn name="name" formula="CONCAT(firstname, lastname)"
dataType="Text" maxLength="100" isMandatory="false"
description="" trimWhitespace="true"/>
</Schema>
The CONCAT function supports more than 2 parameters, and any literal values must be enclosed in single quotes (e.g. 'abc')
This example splits a [Name] column with the format "Lastname, Firstname" into two columns: [First Name] and [Last Name].
<Schema>
<Column name="Name" dataType="Text"/>
<CalculatedColumn name="First Name" ordinal="3" dataType="Text">
<Script>
function firstName(Name) {
return Name.substr(Name.indexOf(', ')+2);
}
function calc(currentRecord) {
if (currentRecord['Name'])
return firstName(currentRecord['Name']);
return '';
}
</Script>
</CalculatedColumn>
<CalculatedColumn name="Last Name" dataType="Text">
<Script>
function lastName(Name) {
return Name.substr(0,Name.indexOf(', '));
}
function calc(currentRecord) {
if (currentRecord['Name'])
return lastName(currentRecord['Name']);
return '';
}
</Script>
</CalculatedColumn>
</Schema>
nameThe user defined name for each calculated column. This is used in <ColumnMapping> when you want to indicate the name of the sourceColumn.
formulaCQL expression used to define formula. Supported functions:
Function | Details |
CONCAT(colA, colB, 'literal value1', 'literal value2') | Concatenates multiple columns, parameters or literal values together. Supports two or more parameters. |
row_number() | This is the numeric row number of files (Excel, delimited, fixed width). Currently not supported in conjunction with other formulas/parameters. |
isnull(colA,'alt value') | If the first column is null, use the second value (can be a literal or another column). |
hash('SHA256',colA) | Hashes the column using the algorithm specified. |
We recommend you salt your value before you hash it.
dataTypeThe data type of each column could be Text, Date, Number or Bool.
maxLengthThe max length of data in the column.
isMandatoryBoolean value determining if the field is a mandatory column to create a row entry.
validateDataBoolean value determining whether or not to validate the data before inserting. Valid data means to fit all the constraints of the column (dataType, maxLength, isMandatory, inputFormat). If the data is not valid and validateData is true, then the entry will not be synced into the table. Also, Execution Errors Table is updated with appropriate Error Type (Invalid Format Exception, Max Length Violation, Mandatory Rule Violation, Input Format Exception)
descriptionDescription of the column.
trimWhitespaceBoolean value determining whether or not to trim white space.
Last modified 8mo ago