Text Related Functions
The following functions can be used within the Calculated Fields section of Data Mapper to returns the first character or characters in a text string, based on the number of characters you specify.
Left([Field],num_chars)
Trims the number of characters from Text from the Left of the string.
For Example “Customer_Name” Field
Left([Customer Name], 3) = ‘Cro’
Right([Field],num_chars)
Trims the number of characters from the Right of the string.
Right([Customer Name], 3) = ‘nc.’
Mid ([Field],start,chars) - same as in excel:
Trims the number of characters from Mid of the string.
Mid([Customer Name], 3,3) = ‘ror’
Split([Field],delim,pos)
Splits the text according to the delimiter, and returns the split value in the given position. A negative position refers to the last elements of the list.
Split([Customer], ' ', 1) = Corore
TextJoin(delimiter, [Field1], [Field2], …) and Join
The TEXTJOIN function combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges.
Example:
TEXTJOIN(" ", [Customer], [Product_Line])
Join(" ", [Customer], [Product_Line])
Logical Functions
IF (cond,value_if_true,[value_if_false])
Tests a user-defined condition and returns one result if the condition is TRUE, and another result if the condition is FALSE.
IF([value]==0,"true", "false")
And([logical1],[logical2]....)
Or([logical1],[logical2]....)
Date Functions
Date (Year,Month,Day)
Creates a date object (type:Date) from the Year,Month and Day
Year: Can be either 4 digits (2019) or 2 digits (19)
Month: Can be either full month name (November), abbreviated (Nov) or a number.
Day: A number
Note: all parameters can be either numbers or text
Month ([field])
Operates on date values only, returns the month part of the Date
Year ([field])
Operates on date values only, returns the year part of the Date
Quarter ([field])
Operates on date values only, returns the quarterpart of the Date
EOMONTH([field]), MONTHEND([field])
Operates on date values only, returns the last day of the month
SOMONTH([field]), MONTHSTART([field])
Operates on date values only, returns the first day of the month
DateOffset([field],days/months/years/weeks/hours/minutes/seconds)
You can use one or more params using their names (i.e. param=value)
DateOffset(2019-08-21 00:00:00,days=-2) = 2019-08-19 00:00:00
DateOffset(2019-08-21 00:00:00,days=-2,years=1) = 2020-08-19 00:00:00
Len([Field])
Len function is a text function that returns the length of a string/ text. Len Function can be used to count the number of characters in a text string and able to count letters, numbers, special characters, non-printable characters, and all spaces from a cell.
Example: Region - East
Len([Region])= 4
Format([field],Directive)
Use on any date field.
To choose which Directive you need please see below:
https://docs.python.org/2/library/time.html#time.strftime
Format(2019-12-01T00:00:00, “%b”) = Dec
Format(2019-12-01T00:00:00, “%B-%Y”) = December-2019
Format(2019-12-01T00:00:00, “%y”) = 19