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.
Trims the number of characters from Text from the Left of the string.
For Example “Customer_Name” Field
Left([Customer Name], 3) = ‘Cro’
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’
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.
TEXTJOIN(" ", [Customer], [Product_Line])
Join(" ", [Customer], [Product_Line])
Tests a user-defined condition and returns one result if the condition is TRUE, and another result if the condition is FALSE.
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
Operates on date values only, returns the month part of the Date
Operates on date values only, returns the year part of the Date
Operates on date values only, returns the quarterpart of the Date
Operates on date values only, returns the last day of the month
Operates on date values only, returns the first day of the month
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 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
Use on any date field.
To choose which Directive you need please see below:
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