v5.0 - v5.5 Data Sync Documentation
  • Overview
  • Release Notes
    • V4.5
    • V4.6
    • v4.7
    • v4.8
    • v4.9
    • v4.10
    • v4.12
    • v4.13
  • v4 Connections Installation Guide
    • v5 Connections and CLI Installation Guide
  • v4 Worker/Listener Installation Guide
    • v5 Worker/Listener Installation Guide
  • Upgrades & Config Changes
  • Builder Guide
    • Overview
    • Types of Data Syncs
    • Subscribing to Event Streams
      • Supported Stream Sources
        • Amazon Simple Queue Service (SQS)
        • Cinchy Change Data Capture
        • Data Polling
        • Kafka Topic
        • MongoDB
        • Salesforce
          • Push Topic
          • Platform Event
    • Configuring a Data Sync
      • Connections Experience & XML Config Reference
        • Info
          • Parameters
            • String Escape
        • Source Config Reference
          • Schema Columns
            • Calculated Column Examples
          • Auth Requests
          • Request Headers
          • Pagination
          • Source Filter
        • Target Destination Config Reference
          • Column Mappings
          • Target Destination Filter
        • Sync Behaviour
        • Post Sync
        • Permissions
        • Jobs
        • Connections Usage Example
      • Data Validation
      • Transformations
        • StringReplacement
      • Supported Data Sources
        • File Based Source
          • Binary File
            • Binary File Data Source Example
          • Delimited File
            • Delimited File Data Source XML Example
          • Fixed Width File
          • Excel
            • Excel Source XML Example
        • Cinchy Event Broker/CDC
          • Cinchy Event Broker/CDC XML Config Example
        • Cinchy Table
          • Cinchy Table XML Config Example
        • Cinchy Query
          • Cinchy Query Data Source Example
        • Copper
        • DB2
        • Dynamics 2015
        • Dynamics
        • DynamoDB
        • Kafka Topic
          • Apache AVRO Data Format
          • Kafka Topic Source Example
        • LDAP
        • MongoDB Collection
          • MongoDB Collection Source XML Example
        • MongoDB Collection (Cinchy Event Triggered)
        • MS SQL Server Query
        • MS SQL Server Table
        • ODBC Table
        • ODBC Query
        • Oracle Table
        • Oracle Query
        • Parquet
        • Polling Event
          • Polling Event Example
        • REST API (Cinchy Event Triggered)
        • REST API
          • REST API XML Example
        • SAP SuccessFactors
        • Salesforce Object (Bulk API)
        • Salesforce Platform Event
        • Salesforce Push Topic
        • Snowflake
          • Snowflake Source XML Example
        • SOAP 1.2 Web Service
      • Supported Sync Targets
        • Cinchy Table
        • DB2 Table
        • Dynamics
        • Kafka Topic
        • MongoDB Collection (Column Based)
        • MS SQL Server Table
        • Oracle Table
        • REST API
        • Salesforce
        • Salesforce Object
        • Snowflake Table
          • Snowflake Table Target XML Example
        • SOAP 1.2 Web Service
    • Common Design Patterns
    • Testing a Data Sync
    • Promoting a Data Sync
    • Scheduling a Data Sync
    • CLI Command List
    • Connections Functions
    • Monitoring
  • Cinchy Platform Documentation
Powered by GitBook
On this page
  • Examples
  • Example 1: XML
  • Example 2: XML
  • Example 3: JavaScript
  • Attributes

Was this helpful?

  1. Builder Guide
  2. Configuring a Data Sync
  3. Connections Experience & XML Config Reference
  4. Source Config Reference
  5. Schema Columns

Calculated Column Examples

This page outlines how to use Calculated Columns.

PreviousSchema ColumnsNextAuth Requests

Last updated 2 years ago

Was this helpful?

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

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.

Example 1: XML

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')

Example 2: XML

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')

Example 3: JavaScript

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>

Attributes

name

formula

CQL 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.

dataType

The data type of each column could be Text, Date, Number or Bool.

maxLength

The max length of data in the column.

isMandatory

Boolean value determining if the field is a mandatory column to create a row entry.

validateData

Boolean 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)

description

Description of the column.

trimWhitespace

Boolean value determining whether or not to trim white space.

The user defined name for each calculated column. This is used in when you want to indicate the name of the sourceColumn.

<ColumnMapping>
Example 1: XML
Example 2: XML
Example 3: JavaScript
Attributes