We're here to help

Data Mapper - Custom Calculated Fields

Avatar
DataRails Support
  • Updated
Follow

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’

Left1.png

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

Split1.png

 

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

Text1.png

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

If1.png

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

 

TIMEDIFF(val1,val2,'type')

Will return the diff between val1 and val2, you can also use minutes, hours, and days.


For Example:

  • TIMEDIFF([Start_Date],[End_date],'days') - will return the number of days between start and end date
  • TIMEDIFF([Start_Date],[End_date],'months') - will return the number of months
  • TIMEDIFF([Start_Date],[End_date],'hours') - will return the number of hours
  • TIMEDIFF([Start_Date],[End_date],'seconds') - will return the number of seconds

 

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

Related articles