Using the Data Mapper, you can customize data by creating Custom Column. Custom columns allow you to add calculated fields using different functions. Learn how to create custom columns before delving into the custom column functions. 

There are three custom function categories:

• Text Functions
• Logical Functions
• Date Functions

Text Functions 

In the Example use the Field 'Customer Name' and the Value 'Corore Ltd'

• Left([Field], num_chars)

  • Description: Returns a specified number of characters from the left side of a text string.

  • Example: Left([Customer Name], 3) = ‘Cor’.

• Right([Field], num_chars)

  • Description: Returns a specified number of characters from the right side of a text string.
  • Example: Right([Customer Name], 3) = ‘Ltd’.
• Mid ([Field], start_num, num_chars)
  • Description: Returns a specific number of characters from a text string, starting at a specified position.
  • Example: Mid([Customer Name], 3, 3) = ‘ror’.
• Find([Field], "find_text", "True")
  • Description: locate the position of a substring within a larger string. Third argument applies case-sensitivity. When there is no match, a null is returned.
  • Example: Find([Customer Name], "C", "True") = 1. However, Find([Customer Name], "c", "True") returns a null, because there is no lower case "c" in the first argument. In such case, write instead Find([Customer Name], "c") = 1.
• Len([Field])
  • Description: The Len function is a text function that returns the length of a string/ text. The Len Function can be used to count the number of characters in a text string and can count letters, numbers, special characters, non-printable characters, and all spaces from a cell.
  • Example: Len([Customer Name])= 10.
• Float([Field])
  • Description: Convert string or number to float number.
  • Example: [Field] =”23.34”. Float( [Field]) = 23.34.
• Int([Field])
  • Description: Convert string or number to integer.
    Example: [Field] =”23.34”. Float( [Field]) = 23.
• Split([Field], delimiter, position)
  • Description: Return split value by delimiter. 
  • Example: Split([Customer Name], ' ', 1) = 'Corore'.
• Substitute([Field], "argument 1", "argument 2","True")
  • Description: Replaces the first argument with the second argument. Third argument applies case-sensitivity. When there is no match for the first argument, the original value is returned.
  • Example: Substitute([Customer Name],”Ltd”,” Limited", "True") = 'Corore Limited'. However, Substitute([Customer Name],”ltd”,” Limited", "True") returns the original value = 'Corore Ltd'. In such case, write instead Substitute([Customer Name],”ltd”,” Limited") = 'Corore Limited'.
• TEXTJOIN/Join(delimiter, [Field1], [Field2], …)
  • Description: Combine multiple text strings into a single string, with a specified delimiter separating each text component.
  • Example: [Country] = "UK". TEXTJOIN(" ", [Customer Name], [Country]) = 'Corore Ltd UK'.

Logical Functions

In the Example use the Field 'Department' and the Value 'Finance'

• IF (condition, value_if_true, value_if_false)
  • Description: Returns one value if a specified condition is true, and another value if that condition is false. can be used with other fields. 
  • Example: IF([Department]=="R&D", “True”, “False”) = 'False'. IF([Department]=="Finance",[Department ID], “NA") = '[Department ID]'.
• And(logical1, logical2, …)
  
  • Description: Used to test multiple conditions simultaneously. It returns TRUE if all the specified conditions are met (i.e., are true), and FALSE if any one of the conditions is not met (i.e., is false).
  • Example (within IF statement): [Country] = "UK". IF(AND([Department] == "Finance"), AND( [Country] == "UK", "True", "False")) = 'True'. 
• Or(logical1, logical2, …)
  
  • Description: Used to test multiple conditions simultaneously. It returns TRUE if at least one of the specified conditions is true, and FALSE if all the conditions are false.
  • Example (within IF statement): [Country] = "UK". IF(OR([Department] == "R&D", [Country] == "US"), "True", "False") = 'False'

• IsNull([Field])

  • Description: Determine whether an expression is NULL and returns TRUE. Otherwise, returns FALSE.
  • Example: IsNull([Department]) = 'False'.

• IsDate([Field])

  • Description: Returns True if the Field’s value format can be recognized as a Date. Otherwise, returns “False”.
  • Example: [Reporting Month] = 31/01/2024. IsDate([Department]) = 'False'. IsDate([Reporting Month]) = 'True'.

• IsNumber([Field])

  • Description: Returns True if the Field’s value format can be recognized as a Number (Integer or Float). Otherwise, returns “False”.
  • Example: [Amount] = 20. IsNumber([Department]) = 'False'. IsNumber([Amount]) = 'True'.

• Match([Field], “Wildcard”)

  • Description: Returns True if the Field’s value matches the Wildcard, Otherwise, returns “False”.
  • Example: Use Wildcard syntax to create the pattern:
    • * matches everything
    • ? matches any single character
    • [seq] matches any character in seq
    • [!seq] matches any char not in seq 
  • Make sure you wrap wildcards with quotation (“ ”) marks. Example: If the Customer field value is Bloomberg, type Match([Customer],“Bl?om*”) = True.

• PatternMatch([Field],"^pattern1 Pattern2 Patt3.*")

  • Description: Returns True if the Field’s value matches the Regex Pattern. Otherwise, returns “False”.
  • Example: Use Regex syntax to create the pattern.
  • For Pattern Matches, make sure you wrap wildcards with quotation (“ ”) marks. Use Regex Syntax to create the pattern. See full Python symbols list here.

• ChooseNotNull([Field1], [Field2], [Field3]….)

  • Description: Returns the first non-null value, from left to right.
  • Example: Example: If [Debit] == [null], [Credit] == 36.7, ChooseNotNull([Debit], [Credit]) = 36.7.

• ChooseDate([Field1], [Field2], [Field3]…)

  • Description: Returns the first Date format value, from left to right.
  • Example: Example: If [Posting date] = “Petty cash”, [Payment Date] = 07/01/2024, ChooseNotDate([Posting date] , [Payment Date]) = 07/01/2024.

• ChooseNotDate ([Field1], [Field2], [Field3]…)

  • Description: Returns the first Non-Date format value, from left to right.
  • Example: If [Posting date] = “Petty cash”, [Payment Date] = 07/01/2024,
    ChooseNotDate([Posting date] , [Payment Date]) = “Petty cash”.

• ChooseNumber([Field1], [Field2], [Field3]…)

  • Description: Returns the first “Integer” or “Float” format value, from left to right.
  • Example: If [Posting date] = “Petty cash”, [Payment Date] = 07/01/2024, [Amount] = 52.2. ChooseNumber ([Posting date] , [Payment Date], [Amount]) = 52.2.

• ChooseNotNumber([Field1], [Field2], [Field3]…)

  • Description: Returns the first non "Integer” or “Float” format value, from left to right.
  • Example: If [Amount] = 52.2 ,[Posting date] = “Petty cash”, [Payment Date] = 07/01/2024, ChooseNotNumber ([Amount] ,[Posting date] , [Payment Date]) = “Petty cash”.

• ChooseMatch (“Wildcard”,[Field1],[Field2]…)

  • Description: Returns the first value that matches the pattern, Use wildcard syntax to create the pattern:
    • * matches everything
    • ? matches any single character
    • [seq] matches any character in seq
    • [!seq] matches any char not in seq
  • Example: If Amount] = 52.2,[Posting date] = “Petty cash”, [Payment Date] = 07/01/2024, ChooseMatch (“Petty”, [Amount],[ Posting date], [Payment Date])= “Petty cash”.

• ChooseNotMatch(“Wildcard”,[Field1],[Field2]…)

  • Description: Returns the first value that does not match the pattern, Use wildcard syntax (left to right) to create the pattern:
    * * matches everything
    * ? matches any single character
    * [seq] matches any character in seq
    * [!seq] matches any char not in seq
  • Example: If [Posting date] = “Petty cash”, [Payment Date] = 07/01/2024, [Amount] = 52.2 ChooseNotMatch (“Pe?ty*”, [Posting date], [Payment Date], , [Amount])= 07/01/2024".

• ChoosePatternMatch (“Pattern”,[Field1],[Field2]…)

  • Description: Returns the first value that matches the pattern. Use wildcard syntax (left to right) to create the pattern.
  • Example: If [Amount] = 52.2 ,[Posting date] = “Petty cash”, [Payment Date] = 07/01/2024, ChoosePatternMatch ("^Petty*", [Amount],[ Posting date], [Payment Date])= “Petty cash”.
  • For Pattern Matches, make sure you wrap wildcards with quotation (“ ”) marks. Use Regex Syntax to create the pattern. See full Python symbols list here.

• ChooseNotPatternMatch(“Pattern”,[Field1],[Field2]…)

  • Description: Returns the first value that does not match the pattern. Use wildcard syntax (left to right) to create the pattern.
  • Example: If [Amount] = 52.2 ,[Posting date] = “Petty cash”, [Payment Date] = 07/01/2024, ChooseNotPatternMatch ("^Petty*", [ Posting date], [Amount], [Payment Date])= “52.2”.
  • For Pattern Matches, make sure you wrap wildcards with quotation (“ ”) marks. Use Regex Syntax to create the pattern. See full Python symbols list here.

Date Functions

In the Example use the Field 'Reporting Month' and the Value '23/01/2024'

• Date (Year, Month, Day)

  • Description: Creates a date object (type: Date) from the Year, Month and Day.
  • Example: Date(2024,01,23) = '23/01/2024'.

• Month ([field])

  • Description: Operates on date values only, returns the month part of the Date.
  • Example: Month([Reporting Month]) = '01' / 'January'.

• FY_Month ([field])

  • Description: Operates on date values only, returns the fiscal month for each date field.

• Year ([field])

  • Description: Operates on date values only, returns the year part of the Date.

• FY_Year ([field])

  • Description: Operates on date values only, returns the fiscal year for each date field.

• Quarter ([field])

  • Description: Operates on date values only, returns the quarter part of the Date.

• FY_Quarter ([field])

  • Description: Operates on date values only, returns the fiscal quarter part of the Date.

• TODAY()

  • Description: Returns the current date.

• EOMONTH([field])

  • Description: MONTHEND Operates on date values only, returns the last day of the month.
  • Example: EOMONTH([Reporting Month]) = 31/01/2024.

• SOMONTH([field]), MONTHSTART ([field])

  • Description: Operates on date values only, returns the first day of the month.
  • Example: SOMONTH([Reporting Month]) = 01/01/2024.

• DateOffset([field], days/months/years/weeks/hours/minutes/seconds)

  • Description: Use one or more parameters using their names (i.e. param=value).
  • Example 1: DateOffset([Reporting Month], days=-2) = 2024-01-21 00:00:00.
  • Example 2: DateOffset([Reporting Month], days=-2, years=1) = 2025-01-21 00:00:00

• TIMEDIFF(val1,val2,"type")

  • Description: The TIMEDIFF function returns the difference between a start date and an end date in the specified time unit. To get a positive result, use the end date as the first argument. To get a negative result, use the start date as the first argument. Ensure that date format values are used.
  • Example 1: TIMEDIFF([End Date], [Start Date], "days") - returns the number of days.
  • Example 2: TIMEDIFF([End Date], [Start Date], "months") - returns the number of months.
  • Example 3: TIMEDIFF([End Date], [Start Date], "hours") - returns the number of hours.
  • Example 4: TIMEDIFF([End Date], [Start Date], "seconds") - returns the number of seconds.
• TIMESTAMPTODATE([field])
  • Description: Use to convert "timestamp" (format must be "whole numbers")  to a date value.
  • Example :TIMESTAMPTODATE(1706042512) = 2024-01-23T 00:00:00.

• Format([field],Directive)

  • Description: Use on any date field.
  • Example 1: Format(2024-12-01T00:00:00, “%b”) = Dec;
  • Example 2: Format(2024-12-01T00:00:00, “%B-%Y”) = December-2024;
  • Example 3: Format(2024-12-01T00:00:00, “%y”) = 19.

Updated August 14, 2024 14:25