Visier Extraction Language (VEL)

Use the Visier Extraction Language to extract and transform data in Visier.

Overview

Use the Visier Extraction Language (VEL) to extract or transform records from your source data in Visier. VEL formulas are written as a single expression that extract or transform records in sources. In Visier, you can use the VEL in the following places:

  • In a mapping’s record filter to select or ignore records in a source.

  • In formulas used to map properties from source data.

  • In data transfers to filter row data.

For more information about adding and configuring mappings, see Add a Mapping.

Basic syntax

Read column values from your data source so that you can alter them when necessary.

column

Returns the value of a column.

You can optionally specify whether the column is optional with the isOptional parameter, where true is optional and false is mandatory. Default is false. This is useful if you have non-mandatory columns defined on the source.

  • If isOptional is true and the column doesn't exist in the source file, the rule returns none for each row because the column is optional.
  • If isOptional is false and the column doesn't exist in the source file, the rule does nothing because the column is mandatory.

Get the value of the "FirstName" column.

Parameter types: (columnName, [isOptional])

Copy 
column("FirstName") // Equivalent to column("FirstName", false) because the default for isOptional is false.

Get the value of the "FirstName2" column, or the "FirstName" column if "FirstName2" doesn't exist.

Copy 
column("FirstName2", true) orElse column("FirstName")

dateColumn

Returns a date value based on the format defined in the source column settings.

You can optionally specify whether the column is optional with the isOptional parameter, where true is optional and false is mandatory. Default is false. This is useful if you have non-mandatory columns defined on the source.

  • If isOptional is true and the column doesn't exist in the source file, the rule returns none for each row because the column is optional.
  • If isOptional is false and the column doesn't exist in the source file, the rule does nothing because the column is mandatory.

Get "Birthdate" value in the format defined in the source column settings.

Parameter types: (columnName, [isOptional])

Copy 
dateColumn("Birthdate")

doubleColumn

Returns the value of a double column.

You can optionally specify whether the column is optional with the isOptional parameter, where true is optional and false is mandatory. Default is false. This is useful if you have non-mandatory columns defined on the source.

  • If isOptional is true and the column doesn't exist in the source file, the rule returns none for each row because the column is optional.
  • If isOptional is false and the column doesn't exist in the source file, the rule does nothing because the column is mandatory.

Get "salary" as a double value.

Parameter types: (columnName, [isOptional])

Copy 
doubleColumn("Salary")

intColumn

Returns the value of a integer column.

You can optionally specify whether the column is optional with the isOptional parameter, where true is optional and false is mandatory. Default is false. This is useful if you have non-mandatory columns defined on the source.

  • If isOptional is true and the column doesn't exist in the source file, the rule returns none for each row because the column is optional.
  • If isOptional is false and the column doesn't exist in the source file, the rule does nothing because the column is mandatory.

Get "performance" as an integer value.

Parameter types: (columnName, [isOptional])

Copy 
intColumn("performance")

booleanColumn

Returns the value of a Boolean column.

You can optionally specify whether the column is optional with the isOptional parameter, where true is optional and false is mandatory. Default is false. This is useful if you have non-mandatory columns defined on the source.

  • If isOptional is true and the column doesn't exist in the source file, the rule returns none for each row because the column is optional.
  • If isOptional is false and the column doesn't exist in the source file, the rule does nothing because the column is mandatory.

Get "isFullTime" as a Boolean value.

Parameter types: (columnName, [isOptional])

Copy 
booleanColumn("isFulltime")

jsonColumn

Returns the value from a JSON column.

Note: Valid in column formulas; not supported in record or row filters.

You can optionally specify whether the column is optional with the isOptional parameter, where true is optional and false is mandatory. Default is false. This is useful if you have non-mandatory columns defined on the source.

  • If isOptional is true and the column doesn't exist in the source file, the rule returns none for each row because the column is optional.
  • If isOptional is false and the column doesn't exist in the source file, the rule does nothing because the column is mandatory.

Get the name value from the JSON object stored in the "Employee Information" column.

Parameter types: (columnName, jsonKeyName, [isOptional])

Copy 
jsonColumn("Employee Information", "name")

doubleJsonColumn

Returns a double value from a JSON column.

Note: Valid in column formulas; not supported in record or row filters.

You can optionally specify whether the column is optional with the isOptional parameter, where true is optional and false is mandatory. Default is false. This is useful if you have non-mandatory columns defined on the source.

  • If isOptional is true and the column doesn't exist in the source file, the rule returns none for each row because the column is optional.
  • If isOptional is false and the column doesn't exist in the source file, the rule does nothing because the column is mandatory.

Get the salary value from the JSON object stored in the "Employee Information" column.

Parameter types: (columnName, jsonKeyName, [isOptional])

Copy 
doubleJsonColumn("Employee Information", "Salary")

intJsonColumn

Returns an integer value from a JSON column.

Note: Valid in column formulas; not supported in record or row filters.

You can optionally specify whether the column is optional with the isOptional parameter, where true is optional and false is mandatory. Default is false. This is useful if you have non-mandatory columns defined on the source.

  • If isOptional is true and the column doesn't exist in the source file, the rule returns none for each row because the column is optional.
  • If isOptional is false and the column doesn't exist in the source file, the rule does nothing because the column is mandatory.

Get the performance value from the JSON object stored in the "Employee Information" column.

Parameter types: (columnName, jsonKeyName, [isOptional])

Copy 
intJsonColumn("Employee Information", "performance")

booleanJsonColumn

Returns a Boolean value from a JSON column.

Note: Valid in column formulas; not supported in record or row filters.

You can optionally specify whether the column is optional with the isOptional parameter, where true is optional and false is mandatory. Default is false. This is useful if you have non-mandatory columns defined on the source.

  • If isOptional is true and the column doesn't exist in the source file, the rule returns none for each row because the column is optional.
  • If isOptional is false and the column doesn't exist in the source file, the rule does nothing because the column is mandatory.

Get the "isFullTime" value from the JSON object stored in the "Employee Information" column.

Parameter types: (columnName, jsonKeyName, [isOptional])

Copy 
booleanJsonColumn("Employee Information", "isFulltime")

dateJsonColumn

Returns a date value based on the format defined in the source column settings.

Note: Valid in column formulas; not supported in record or row filters.

You can optionally specify whether the column is optional with the isOptional parameter, where true is optional and false is mandatory. Default is false. This is useful if you have non-mandatory columns defined on the source.

  • If isOptional is true and the column doesn't exist in the source file, the rule returns none for each row because the column is optional.
  • If isOptional is false and the column doesn't exist in the source file, the rule does nothing because the column is mandatory.

Get the "HiredDate" value from the JSON object stored in the "Employee Information" column.

Parameter types: (columnName, jsonKeyName, [isOptional])

Copy 
dateJsonColumn("Employee Information", "HiredDate")

jsonPathColumn

Returns a value described by a JSONPath.

You can optionally specify whether the column is optional with the isOptional parameter, where true is optional and false is mandatory. Default is false. This is useful if you have non-mandatory columns defined on the source.

  • If isOptional is true and the column doesn't exist in the source file, the rule returns none for each row because the column is optional.
  • If isOptional is false and the column doesn't exist in the source file, the rule does nothing because the column is mandatory.

Get the "workReferenceEnumeration" value from the "workdayId" column value.

Parameter types: (columnName, jsonPathString, [isOptional])

Copy 
jsonPathColumn("workdayId", "$.id[0].type.workerReferenceEnumeration")

intJsonPathColumn

Returns a integer value described by a JSONPath.

You can optionally specify whether the column is optional with the isOptional parameter, where true is optional and false is mandatory. Default is false. This is useful if you have non-mandatory columns defined on the source.

  • If isOptional is true and the column doesn't exist in the source file, the rule returns none for each row because the column is optional.
  • If isOptional is false and the column doesn't exist in the source file, the rule does nothing because the column is mandatory.

Get the "id" value from the "workdayId" column value.

Parameter types: (columnName, jsonPathString, [isOptional])

Copy 
intJsonPathColumn("workdayId", "$.id[1].number")

doubleJsonPathColumn

Returns a double value described by a JSONPath.

You can optionally specify whether the column is optional with the isOptional parameter, where true is optional and false is mandatory. Default is false. This is useful if you have non-mandatory columns defined on the source.

  • If isOptional is true and the column doesn't exist in the source file, the rule returns none for each row because the column is optional.
  • If isOptional is false and the column doesn't exist in the source file, the rule does nothing because the column is mandatory.

Get the "previousSalary" value from the "salaryHistory" column value.

Parameter types: (columnName, jsonPathString, [isOptional])

Copy 
doubleJsonPathColumn("salaryHistory", "$.previousCompensation[0].amount")

booleanJsonPathColumn

Returns a Boolean value described by a JSONPath.

You can optionally specify whether the column is optional with the isOptional parameter, where true is optional and false is mandatory. Default is false. This is useful if you have non-mandatory columns defined on the source.

  • If isOptional is true and the column doesn't exist in the source file, the rule returns none for each row because the column is optional.
  • If isOptional is false and the column doesn't exist in the source file, the rule does nothing because the column is mandatory.

Determine whether the employee working remotely as of their most recent location.

Parameter types: (columnName, jsonPathString, [isOptional])

Copy 
booleanJsonPathColumn("location", "$.history[0].remote")

dateJsonPathColumn

Returns a date value described by a JSONPath.

You can optionally specify whether the column is optional with the isOptional parameter, where true is optional and false is mandatory. Default is false. This is useful if you have non-mandatory columns defined on the source.

  • If isOptional is true and the column doesn't exist in the source file, the rule returns none for each row because the column is optional.
  • If isOptional is false and the column doesn't exist in the source file, the rule does nothing because the column is mandatory.

Get the completion date for most recent education that the applicant has completed.

Parameter types: (columnName, jsonPathString, [isOptional])

Copy 
dateJsonPathColumn("education", "$.history[0].completed_at")

delimitedMultiValueColumn

Create separate rows for each delimiter-separated value.

Note:  

  • You can use functions that split rows like delimitedMultiValueColumn once per mapping.
  • Valid in column formulas; not supported in record or row filters.

You can optionally specify whether the column is optional with the isOptional parameter, where true is optional and false is mandatory. Default is false. This is useful if you have non-mandatory columns defined on the source.

  • If isOptional is true and the column doesn't exist in the source file, the rule returns none for each row because the column is optional.
  • If isOptional is false and the column doesn't exist in the source file, the rule does nothing because the column is mandatory.

Split "Location" in to separate rows where-ever multiple locations are listed and separated by a semi-colon.

Parameter types: (columnName, delimiterString, [isOptional])

Copy 
delimitedMultiValueColumn("Location", ";")

identity

Returns the default value as untransformed input. This is most useful in conjunction with replace whose output is none if the input is present but not matching; if identity is used, non-matching input passes through.

Note: This cannot be used with date rules, orElse, or rules that take a collection of inputs.

none

Applies the value of none when the expression fails to resolve.

Note: Using the value none as a default value is the same as not specifying a default.

lookup

Returns a single column value from a file based on matching keys. You can optionally return a defaultValue as a fallback value.

Get the "Employee_ID" value from the "Employee_Id_Lookup_File" for the current record, based on matching keys.

Parameter types: (column, file, [defaultValue])

Copy 
lookup(column("Employee_ID"), file("Employee_Id_Lookup_File"))

file

Defines the file in a lookup.

Note: Valid in column formulas; not supported in record or row filters.

Get the "Employee_ID" value from the "Employee_Id_Lookup_File" for the current record, based on matching keys.

Parameter types: (fileString)

Copy 
lookup(column("Employee_ID"), file("Employee_Id_Lookup_File"))

meltColumns

Returns the column names.

Get the column names.

Copy 
meltColumns()

Get the column values.

Copy 
column(meltColumns())

rowNumber

Returns the index of the row being processed.

Get the index of the row currently being processed.

Copy 
rowNumber()

filePath

Returns the path to the input file.

Get the path to the input file.

Copy 
filePath()

fileName

Returns the name of the input file.

Get the name of the input file.

Copy 
fileName()

if

Keyword for starting a conditional statement. This is used with else.

Set the salary to the column value when the value is greater than 0, otherwise set to 0.00.

Copy 
if(doubleColumn("salary") > 0)
    doubleColumn("salary")
else
    0.00

else

Keyword to define the fallback for a conditional statement. This is used with if.

Set the salary to the column value when the value is greater than 0, otherwise set to 0.00.

Copy 
if(doubleColumn("salary") > 0)
    doubleColumn("salary")
else
    0.00

Conversion functions

Convert column values to specific data types so that you can perform additional operations. For example, convert a string to an integer so that you can perform additional mathematical calculations and produce new derived values.

toString

Converts a value to a string. You can optionally return a defaultValue when the conversion fails, otherwise none is returned.

Convert the 2.2 double to a string value.

Parameter types: (value, [defaultValue])

Copy 
toString(2.2)

toDouble

Converts a value to a double. You can optionally return a defaultValue when the conversion fails, otherwise none is returned.

Convert the "2.0" string to a double value.

Parameter types: (string, [defaultValue])

Copy 
toDouble("2.0")

toInt

Converts a value to an integer. You can optionally return a defaultValue when the conversion fails, otherwise none is returned.

Convert the "2" string to an integer value.

Parameter types: (string, [defaultValue])

Copy 
toInt("2")

toBoolean

Converts a value to a Boolean. You can optionally return a defaultValue when the conversion fails, otherwise none is returned.

Convert the "True" string to a Boolean value.

Parameter types: (string, [defaultValue])

Copy 
toBoolean("True")

toDate

Converts a value to a date. You can optionally return a defaultValue when the conversion fails, otherwise none is returned.

Convert the "HireDate" column to a date value in the "MM/dd/yyyy" format.

Parameter types: (string, dateFormat, [defaultValue])

Copy 
toDate(column("HireDate"), dateFormat("MM/dd/yyyy"), "01/09/2022")

intToDouble

Converts an integer to a double. You can optionally return a defaultValue when the conversion fails, otherwise none is returned.

Convert the integer value of 2 in to a double.

Parameter types: (integer, [defaultValue])

Copy 
intToDouble(2)

doubleToInt

Converts a double to an integer. You can optionally return a defaultValue when the conversion fails, otherwise none is returned.

Convert the 2.0 double value to an integer.

Parameter types: (double, [defaultValue])

Copy 
doubleToInt(2.0)

dateToString

Converts a date to a string in the specified format.

Convert the "Birth_Date" value to a string in the "MMM dd, yyyy" format.

Parameter types: (dateObject, dateFormat)

Copy 
dateToString(date(column("Birth_Date"), dateFormat("yyyy-MM-dd")), dateFormat("MMM dd, yyyy"))

dateToInt

Converts a date to an integer.

Convert the "EffectiveDate" value to an integer.

Parameter types: (dateObject)

Copy 
dateToInt(toDate(toString(column("EffectiveDate")), dateFormat("yyyy-MM-dd HH:mm:ss")))

utcEpochToDate

Converts an epoch timestamp to a date.

Convert an epoch timestamp to a date.

Parameter types: (epochInMillisecondsString)

Copy 
utcEpochToDate("1712095588000")

Rule functions

Define the column values to keep, replace, or skip.

keep

Return a value that matches a member of the collection provided, otherwise return none.

Get the "Gender" column value where it equals "M" or "F", otherwise return none.

Parameter types: (column, collection)

Copy 
keep(column("Gender"), { "M", "F" })

replace

Replaces values that match members in a collection.

Replace "M" with "Male" and "F" with "Female".

Parameter types: (column, collection)

Copy 
replace(column("Gender"), { "M" -> "Male", "F" -> "Female" })

skipNone

Skips extracted none values.

Skip the "Salary" column when the value is set to none.

Parameter types: (column)

Copy 
skipNone(intColumn("Salary"))

takeNotEmpty

Accepts a collection of columns and returns the first column value that isn't a none value. You can optionally return a defaultValue when all the columns return none, otherwise none is returned.

Get the first column value that isn't none from the "EventReason1", "EventReason2", and "EventReason3" columns, or return "Unknown Reason" when all of the columns return none.

Parameter types: (collection, [defaultValue])

Copy 
takeNotEmpty({ column("EventReason1"), column("EventReason2"), column("EventReason3") }, "Unknown Reason")

keepSuccessOnly

Skip the incorrect or empty cell value if the default value is not none.

Skip the "Salary" column when the value is the default, and the default value is not none.

Parameter types: (column)

Copy 
keepSuccessOnly(intColumn("Salary"))

Math operators

Use math operators to perform mathematical calculations.

+

Adds numbers.

Get "totalCompensation" by adding the "baseSalary" and "bonus" column values.

Copy 
totalCompensation := doubleColumn("baseSalary") + doubleColumn("bonus")

-

Subtracts numbers.

Get "baseSalary" by subtracting the "bonus" column value from the "totalCompensation" column value.

Copy 
baseSalary := doubleColumn("totalCompensation") - doubleColumn("bonus")

*

Multiplies numbers.

Get "overtimeCompensation" by multiplying the "overtimeHours" column value, "hourlyRate" column value and 1.5.

Copy 
totalOvertimeCompensation := (intColumn("overtimeHours") * doubleColumn("hourlyRate")) * 1.5

/

Divides numbers.

Get "overtimeHours" by dividing "totalOvertimeCompensation" and the overtime hourly rate.

Copy 
overtimeHours := doubleColumn("totalOvertimeCompensation") / (doubleColumn("hourlyRate")) * 1.5)

Comparison operators

Use comparison operators to determine how two values compare.

=

Compares two values and determines whether they are equal.

Return "Male" when the "Gender" column value is equal to "M", otherwise return "Other".

Copy 
if (column("Gender") = "M") "Male" else "Other"

!=

Compares two values and determines whether they are not equal.

Return "Other" when the "Gender" column value is not equal to "M", otherwise return "Male".

Copy 
if (column("Gender") != "M") "Other" else "Male"

>

Compares two values and determines whether the left value is greater than the right value.

Return "Full Time" when the "FTE Factor" column value is greater than 9, otherwise return "Part Time".

Copy 
if (intColumn("FTE Factor") > 9) "Full Time" else "Part Time"

<

Compares two values and determines whether the left value is less than the right value.

Return "Part Time" when the "FTE Factor" column value is less than 9, otherwise return "Full Time".

Copy 
if (intColumn("FTE Factor") < 9) "Part Time" else "Full Time"

>=

Compares two values and determines whether the left value is greater than or equal to the right value.

Return "Full Time" when the "FTE Factor" column value is greater than or equal to 9, otherwise return "Part Time".

Copy 
if (intColumn("FTE Factor") >= 9) "Full Time" else "Part Time"

<=

Compares two values and determines whether the left value is less than or equal to the right value.

Return "Part Time" when the "FTE Factor" column value is less than or equal to 9, otherwise return "Full Time".

Copy 
if (intColumn("FTE Factor") <= 9) "Part Time" else "Full Time"

Logical operators

Use logical operators to compare expressions and return a Boolean value.

and

Compares two expressions and returns true if both are true. This is interchangeable with &&.

Return "Lives in Vancouver, Canada" if the "country" column value is equal to "Canada" and the "city" column value is equal to "Vancouver", otherwise return "Lives elsewhere."

Copy 
if ((column("country") = "Canada") and (column("city") = "Vancouver")) 
    "Lives in Vancouver, Canada." 
else 
    "Lives elsewhere."

&&

Compares two expressions and returns true if both are true. This is interchangeable with and.

Return "Lives in Vancouver, Canada" if the "country" column value is equal to "Canada" and the "city" column value is equal to "Vancouver", otherwise return "Lives elsewhere."

Copy 
if ((column("country") = "Canada") && (column("city") = "Vancouver")) 
    "Lives in Vancouver, Canada." 
else 
    "Lives elsewhere."

or

Compares two expressions and returns true if either is true. This is interchangeable with ||.

Return "Lives in North America" if the "country" column value is equal to "Canada", "United States" or "Mexico", otherwise return "Lives elsewhere."

Copy 
if ((column("country") = "Canada") or (column("country") = "United States") or (column("country") = "Mexico")) 
    "Lives in North America." 
else 
    "Lives elsewhere."

||

Compares two expressions and returns true if either is true. This is interchangeable with or.

Return "Lives in North America" if the "country" column value is equal to "Canada", "United States" or "Mexico", otherwise return "Lives elsewhere."

Copy 
if ((column("country") = "Canada") || (column("country") = "United States") || (column("country") = "Mexico")) 
    "Lives in North America." 
else 
    "Lives elsewhere."

not

Compares two expressions and returns true if not true. This is interchangeable with !.

Return "Lives elsewhere" if the "country" column value is not "Canada", "United States" or "Mexico", otherwise return "Lives in North America."

Copy 
if (not ((column("country") = "Canada") or (column("country") = "United States") or (column("country") = "Mexico"))) 
     "Lives elsewhere."
else 
    "Lives in North America."

!

Compares two expressions and returns true if not true. This is interchangeable with not.

Return "Lives elsewhere" if the "country" column value is not "Canada", "United States" or "Mexico", otherwise return "Lives in North America."

Copy 
if (!((column("country") = "Canada") or (column("country") = "United States") or (column("country") = "Mexico"))) 
     "Lives elsewhere."
else 
    "Lives in North America."

inSet

Compares a column to a set of values and returns true if there is a match.

Return the "Job Title" column value if the "Job Title" column value equals "Sales" or "Marketing".

Parameter types: (column, values)

Copy 
if (inSet(column("Job Title"), {"Sales", "Marketing"}) column("Job Title") else ""

Date functions

Use date functions to create date objects, format date objects, shift dates and get points in time related to your data source and tenant.

date

Converts a string to a date object. This is interchangeable with toDate. You can optionally return a defaultValue when the dateString is invalid.

Get the "Birthdate" column value as a date object in "MM/dd/yyyy" format.

Parameter types: (dateString, dateFormat, [defaultValue])

Copy 
date(column("Birthdate"), dateFormat("MM/dd/yyyy"))

dateToUTC

Converts the date to the UTC equivalent. You can optionally return a defaultValue when the dateString is invalid.

Note: If specified, defaultValue is used as-is, and not converted to UTC.

Get the UTC equivalent for the "EventDate" column where the original timezone is the "Timezone" column value, otherwise return the defaultDate column value.

Parameter types: (dateString, timezoneString, [defaultValue])

Copy 
dateToUTC(dateColumn("EventDate"), Column("Timezone"), dateColumn("DefaultDate"))

dateFormat

Specifies the format for a date object. You can optionally return a defaultValue when the dateString is invalid.

Note: Date formats are based on the Java SimpleDateFormat specification.

Get the "Birthdate" column value as a date object in "MM/dd/yyyy" format.

Parameter types: (dateString, dateFormat, [defaultValue])

Copy 
date(column("Birthdate"), dateFormat("MM/dd/yyyy"))

shiftMillis

Shifts a date by a number of milliseconds. You can optionally return a defaultValue when the dateString is invalid.

Get the "HireDate" in "MM/dd/yyyy" format shifted forward by one millisecond, otherwise return "01/09/2023".

Parameter types: (dateString, dateFormat, offset, [defaultValue])

Copy 
shiftMillis(column("HireDate"), dateFormat("MM/dd/yyyy"), 1, "01/09/2023")

shiftSeconds

Shifts a date by a number of seconds. You can optionally return a defaultValue when the dateString is invalid.

Get the "HireDate" in "MM/dd/yyyy" format shifted forward by one second, otherwise return "01/09/2023".

Parameter types: (dateString, dateFormat, offset, [defaultValue])

Copy 
shiftSeconds(column("HireDate"), dateFormat("MM/dd/yyyy"), 1, "01/09/2023")

shiftMinutes

Shifts a date by a number of minutes. You can optionally return a defaultValue when the dateString is invalid.

Get the "HireDate" in "MM/dd/yyyy" format shifted forward by one minute, otherwise return "01/09/2023".

Parameter types: (dateString, dateFormat, offset, [defaultValue])

Copy 
shiftMinutes(column("HireDate"), dateFormat("MM/dd/yyyy"), 1, "01/09/2023")

shiftHours

Shifts a date by a number of hours. You can optionally return a defaultValue when the dateString is invalid.

Get the "HireDate" in "MM/dd/yyyy" format shifted forward by one hour, otherwise return "01/09/2023".

Parameter types: (dateString, dateFormat, offset, [defaultValue])

Copy 
shiftHours(column("HireDate"), dateFormat("MM/dd/yyyy"), 1, "01/09/2023")

shiftDays

Shifts a date by a number of days. You can optionally return a defaultValue when the dateString is invalid.

Get the "HireDate" in "MM/dd/yyyy" format shifted forward by one day, otherwise return "01/09/2023".

Parameter types: (dateString, dateFormat, offset, [defaultValue])

Copy 
shiftDays(column("HireDate"), dateFormat("MM/dd/yyyy"), 1, "01/09/2023")

shiftWeeksWithCustomCalendar

Shifts a date by a number of weeks. You can optionally return a defaultValue when the dateString is invalid.

Get the "HireDate" in "MM/dd/yyyy" format shifted forward by one week, otherwise return "01/09/2023".

Parameter types: (dateString, dateFormat, offset, [defaultValue])

Copy 
shiftWeeksWithCustomCalendar(column("HireDate"), dateFormat("MM/dd/yyyy"), 1, "01/09/2023")

shiftMonths

Shifts a date by a number of months. You can optionally return a defaultValue when the dateString is invalid.

Get the "HireDate" in "MM/dd/yyyy" format shifted forward by one month, otherwise return "01/09/2023".

Parameter types: (dateString, dateFormat, offset, [defaultValue])

Copy 
shiftMonths(column("HireDate"), dateFormat("MM/dd/yyyy"), 1, "01/09/2023")

shiftMonthsWithCustomCalendar

Shifts a date by a number of months. You can optionally return a defaultValue when the dateString is invalid.

Get the "HireDate" in "MM/dd/yyyy" format shifted forward by one month, otherwise return "01/09/2023".

Parameter types: (dateString, dateFormat, offset, [defaultValue])

Copy 
shiftMonthsWithCustomCalendar(column("HireDate"), dateFormat("MM/dd/yyyy"), 1, "01/09/2023")

shiftYears

Shifts a date by a number of years. You can optionally return a defaultValue when the dateString is invalid.

Get the "HireDate" in "MM/dd/yyyy" format shifted forward by one year, otherwise return "01/09/2023".

Parameter types: (dateString, dateFormat, offset, [defaultValue])

Copy 
shiftYears(column("HireDate"), dateFormat("MM/dd/yyyy"), 1, "01/09/2023")

shiftYearsWithCustomCalendar

Shifts a date by a number of years. You can optionally return a defaultValue when the dateString is invalid.

Get the "HireDate" in "MM/dd/yyyy" format shifted forward by one year, otherwise return "01/09/2023".

Parameter types: (dateString, dateFormat, offset, [defaultValue])

Copy 
shiftYearsWithCustomCalendar(column("HireDate"), dateFormat("MM/dd/yyyy"), 1, "01/09/2023")

firstDateInMonth

Shifts the date to the beginning of the month. You can optionally return a defaultValue when the dateString is invalid.

Get the first date of the month based on the "HireDate" column value, otherwise return "01/09/2023".

Parameter types: (dateString, dateFormat, [defaultValue])

Copy 
firstDateInMonth(column("HireDate"), dateFormat("MM/dd/yyyy"), "01/09/2023")

firstDateInMonthWithCustomCalendar

Shifts the date to the beginning of the month. You can optionally return a defaultValue when the dateString is invalid.

Get the first date of the month based on the "HireDate" column value, otherwise return "01/09/2023".

Parameter types: (dateString, dateFormat, [defaultValue])

Copy 
firstDateInMonthWithCustomCalendar(column("HireDate"), dateFormat("MM/dd/yyyy"), "01/09/2023")

firstDateInQuarterWithCustomCalendar

Shifts the date to the beginning of the quarter. You can optionally return a defaultValue when the dateString is invalid.

Get the first date of the quarter based on the "HireDate" column value, otherwise return "01/09/2023".

Parameter types: (dateString, dateFormat, [defaultValue])

Copy 
firstDateInQuarterWithCustomCalendar(column("HireDate"), dateFormat("MM/dd/yyyy"), "01/09/2023")

firstDateInWeek

Shifts the date to the beginning the week. You can optionally return a defaultValue when the dateString is invalid.

Note: This rule considers Monday to be the first day of the week. If you require a different day, consider using shiftDays.

Get the first date of the week based on the "HireDate" column value, otherwise return "01/09/2023".

Parameter types: (dateString, dateFormat, [defaultValue])

Copy 
firstDateInWeek(column("HireDate"), dateFormat("MM/dd/yyyy"), "01/09/2023")

firstDateInWeekWithCustomCalendar

Shifts the date to the beginning the week. You can optionally return a defaultValue when the dateString is invalid.

Note: This rule considers Monday to be the first day of the week. If you require a different day, consider using shiftDays.

Get the first date of the week based on the "HireDate" column value, otherwise return "01/09/2023".

Parameter types: (dateString, dateFormat, [defaultValue])

Copy 
firstDateInWeekWithCustomCalendar(column("HireDate"), dateFormat("MM/dd/yyyy"), "01/09/2023")

lastDateInMonth

Shifts the date to the end the month. You can optionally return a defaultValue when the dateString is invalid.

Get the last date of the month based on the "HireDate" column value, otherwise return "01/09/2023".

Parameter types: (dateString, dateFormat, [defaultValue])

Copy 
lastDateInMonth(column("HireDate"), dateFormat("MM/dd/yyyy"), "01/09/2023")

lastDateInWeek

Shifts the date to the end the week. You can optionally return a defaultValue when the dateString is invalid.

Note: This rule considers Sunday to be the last day of the week. If you require a different day, consider using shiftDays.

Get the last date of the week based on the "HireDate" column value, otherwise return "01/09/2023".

Parameter types: (dateString, dateFormat, [defaultValue])

Copy 
lastDateInWeek(column("HireDate"), dateFormat("MM/dd/yyyy"), "01/09/2023")

lastDateInWeekWithCustomCalendar

Shifts the date to the end the week. You can optionally return a defaultValue when the dateString is invalid.

Note: This rule considers Sunday to be the last day of the week. If you require a different day, consider using shiftDays.

Get the last date of the week based on the "HireDate" column value, otherwise return "01/09/2023".

Parameter types: (dateString, dateFormat, [defaultValue])

Copy 
lastDateInWeekWithCustomCalendar(column("HireDate"), dateFormat("MM/dd/yyyy"), "01/09/2023")

lastDateInMonthWithCustomCalendar

Shifts the date to the end the month. You can optionally return a defaultValue when the dateString is invalid.

Get the last date of the month based on the "HireDate" column value, otherwise return "01/09/2023".

Parameter types: (dateString, dateFormat, [defaultValue])

Copy 
lastDateInMonthWithCustomCalendar(column("HireDate"), dateFormat("MM/dd/yyyy"), "01/09/2023")

lastDateInQuarterWithCustomCalendar

Shifts the date to the end the quarter. You can optionally return a defaultValue when the dateString is invalid.

Get the last date of the quarter based on the "HireDate" column value, otherwise return "01/09/2023".

Parameter types: (dateString, dateFormat, [defaultValue])

Copy 
lastDateInQuarterWithCustomCalendar(column("HireDate"), dateFormat("MM/dd/yyyy"), "01/09/2023")

customCalendarDayName

Returns the day name. You can optionally return a defaultValue as a fallback value.

Get the day name for "startDate".

Parameter types: (dateObject, [defaultValue])

Copy 
customCalendarDayName(dateColumn("startDate"))

customCalendarWeekName

Returns the week name. You can optionally return a defaultValue as a fallback value.

Get the week name for "startDate".

Parameter types: (dateObject, [defaultValue])

Copy 
customCalendarWeekName(dateColumn("startDate"))

customCalendarMonthName

Returns the month name. You can optionally return a defaultValue as a fallback value.

Get the month name for "startDate".

Parameter types: (dateObject, [defaultValue])

Copy 
customCalendarMonthName(dateColumn("startDate"))

customCalendarQuarterName

Returns the quarter name. You can optionally return a defaultValue as a fallback value.

Get the month name for "startDate".

Parameter types: (dateObject, [defaultValue])

Copy 
customCalendarQuarterName(dateColumn("startDate"))

customCalendarYearName

Returns the year name. You can optionally return a defaultValue as a fallback value.

Get the year name for "startDate".

Parameter types: (dateObject, [defaultValue])

Copy 
customCalendarYearName(dateColumn("startDate"))

dateFromCustomCalendar

Returns the first calendar date that falls within the constraints provided. You can optionally return a defaultValue as a fallback value.

Get the first date for the year "FY20", quarter "Q4", and month "P11" on the custom calendar.

Parameter types: (stringYear, [stringQuarter], [stringMonth], [defaultValue])

Copy 
dateFromCustomCalendar("FY20", "Q4", "P11")

sourceTimestamp

Returns the "Snapshot time (UTC)" for the uploaded file.

Get the "Snapshot time (UTC)" value.

Copy 
sourceTimestamp()

tenantStartDate

Returns the start date for your tenant.

Get the tenant start date.

Copy 
tenantStartDate()

String functions

Use string functions to work with string values.

leftPadding

Adds a specified character to the left of the designated string until the length of the string reaches the specified length. In cases where the designated string exceeds the specified length, the defaultValue is used.

Get the "Month" column value in a two-digit format.

Parameter types: (sourceString, paddingString, lengthInteger, [defaultValue])

Copy 
leftPadding(column("Month"), "0", 2)

rightPadding

Adds a specified character to the right of the designated string until the length of the string reaches the specified length. In cases where the designated string exceeds the specified length, the defaultValue is used.

Get the "Employee_ID" column value in a ten-digit format.

Parameter types: (sourceString, paddingString, lengthInteger, [defaultValue])

Copy 
rightPadding(column("Employee_ID"), "0", 10)

+

Concatenates string values.

Note: Using + to concatenate strings with none values is not supported and generates an error. If you need to concatenate values that can be none, consider using concat instead.

Get the full name by concatenating the "First Name" column value, " ", and the "Last Name" column value.

Copy 
column("First Name") + " " + column("Last Name")

concat

Concatenates string values. You can optionally return a defaultValue when concatenating with none values, otherwise returns none.

Note: Using concat with any columns that have a none value results in a final value of none.

Get the full name using the "FirstName" and "LastName" column values separated by ", ".

Parameter types: (collection, delimiterString, [defaultValue])

Copy 
concat({ column("FirstName"), column("LastName") }, ", ")

sha256

Computes the SHA-256 hash of a given string, returning the hash as a hexadecimal string.

Compute the SHA-256 hash for the "FirstName" column.

Parameter types: (sourceString)

Copy 
sha256(column("FirstName"))

toLowerCase

Converts a string to lower case. You can optionally return a defaultValue when the conversion fails, otherwise returns none.

Get the "EmployeeID" column value in lower case.

Parameter types: (sourceString, [defaultValue])

Copy 
toLowerCase(column("EmployeeID"))

toUpperCase

Converts a string to upper case. You can optionally return a defaultValue when the conversion fails, otherwise returns none.

Get the "EmployeeID" column value in upper case.

Parameter types: (sourceString, [defaultValue])

Copy 
toUpperCase(column("EmployeeID"))

trimCharactersLeft

Removes characters from the start of a string. You can optionally return a defaultValue when the sourceString is invalid, otherwise returns none.

Get the "Employee_ID" column value without the "EID" prefix.

Parameter types: (sourceString, trimString, [defaultValue])

Copy 
trimCharactersLeft(column("Employee_ID"), "EID", column("Employee_ID"))

trimCharactersRight

Removes characters from the end of a string. You can optionally return a defaultValue when the sourceString is invalid, otherwise returns none.

Get the "Performance" column value without the " Rating" text.

Parameter types: (sourceString, trimString, [defaultValue])

Copy 
trimCharactersRight(column("Performance"), " Rating", column("Performance"))

stringLeft

Returns a subset of characters from the left-side of a string. You can use the result of other functions like indexOf as the number of characters you want to include in your substring. Additionally, you can optionally return a defaultValue when the sourceString is invalid, otherwise returns none.

Get the year from the string value "2024/01/15".

Parameter types: (sourceString, numberOfCharacters, [defaultValue])

Copy 
stringLeft("2024/01/15", 4)

stringRight

Returns a subset of characters from the right-side of a string. You can use the result of other functions like indexOf as the number of characters you want to include in your substring. Additionally, you can optionally return a defaultValue when the sourceString is invalid, otherwise returns none.

Get the "EmployeeID" without the "EID" prefix.

Parameter types: (sourceString, numberOfCharacters, [defaultValue])

Copy 
stringRight(column("EmployeeID"), (length("EmployeeID") - 3))

stringDropLeft

Removes characters from the start of a string. You can use the result of other functions like lastIndexOf as the number of characters you want removed. You can optionally return a defaultValue when the sourceString is invalid, otherwise returns none.

Get the year from the "EventDate" column value by removing the first 6 characters from the date value. This assumes a "dd//mm/yyyy" date format.

Parameter types: (sourceString, numberOfCharacters, [defaultValue])

Copy 
stringDropLeft(column("EventDate"), 6)

stringDropRight

Removes characters from the end of a string. You can use the result of other functions like lastIndexOf as the number of characters you want removed. You can optionally return a defaultValue when the sourceString is invalid, otherwise returns none.

Get the year from the "EventDate" column value by removing the first 6 characters from the date value. This assumes a "yyyy/mm/dd" date format.

Parameter types: (sourceString, numberOfCharacters, [defaultValue])

Copy 
stringDropRight(column("EventDate"), 6)

stringSubstitute

Searches and replaces in a string. You can optionally return a defaultValue when the sourceString, searchString, or replaceString is invalid, otherwise returns none.

Note: This function returns a none value when any of the parameters are set to a none value.

Get the "EmployeeID" column value without the "EID" prefix.

Parameter types: (sourceString, searchString, replaceString, [defaultValue])

Copy 
stringSubstitute(column("EmployeeID"), "EID", "")

stringReplace

Replaces a substring. You can optionally return a defaultValue when the sourceString or replaceString is invalid, otherwise returns none.

Remove the "EID" prefix for the "EmployeeID" column value.

Parameter types: (sourceString, startIndex, numberOfCharacters, replaceString, [defaultValue])

Copy 
stringReplace(column("EmployeeID"), 0, 3, "")

indexOf

Returns the index of the first matched substring. Otherwise, returns -1 if no match is found. Index starts at 0. You can optionally return a defaultValue when the sourceString is invalid, otherwise returns none.

Return "Lives in Canada." if "Canada" is found in the "location" column value.

Parameter types: (sourceString, searchString, startAtPosition, [defaultValue])

Copy 
if(indexOf(column("location"), "Canada") > -1)
    "Lives in Canada."

lastIndexOf

Returns the index of the last matched substring. Otherwise, returns -1 if no match is found. Index starts at 0. You can optionally return a defaultValue when the sourceString is invalid, otherwise returns none.

Return "Lives in Canada." if "Canada" is found in the "location" column value.

Parameter types: (sourceString, searchString, startAtPosition, [defaultValue])

Copy 
if(lastIndexOf(column("location"), "Canada") > -1)
    "Lives in Canada."

length

Returns the length of a string.

Get the length of the "FirstName" column value.

Parameter types: (string)

Copy 
length(column("FirstName"))

capitalizeAll

Converts the first character of all words in a string to upper case, and converts all other characters to lower case.

Capitalize the first character of every word in the "FirstName" column.

Parameter types: (string)

Copy 
capitalizeAll(column("FirstName"))

capitalizeFirstChar

Converts the first character of the first word in a string to upper case. This does not modify other characters.

Capitalize the first character in the "FirstName" column.

Parameter types: (string)

Copy 
capitalizeFirstChar(column("FirstName"))

format

Replaces the %s placeholder value in a string with the first argument provided. You can optionally return a defaultValue when the sourceString is invalid, otherwise returns none.

Get the "FirstName" column value prefixed with "My name is: ".

Parameter types: (sourceString, templateString, [defaultValue])

Copy 
format(column("FirstName"), "My name is: %s", column("LastName"))

Number functions

Use number functions to work with numbers.

min

Returns the lesser of the two values.

Note: Both values must be of the same type.

Get the lesser of the "sickDays" and "personalDays" column values.

Parameter types: (number, number)

Copy 
min(intColumn("sickDays"), intColumn("personalDays"))

max

Returns the greater of the two values.

Note: Both values must be of the same type.

Get the greater of the "sickDays" and "personalDays" column values.

Parameter types: (number, number)

Copy 
max(intColumn("sickDays"), intColumn("personalDays"))

mod

Returns the modulus of two values.

Note: Both values must be of the same type.

Determine whether the number 10 is an even number.

Parameter types: (number, number)

Copy 
if(mod(10, 2) = 0)
    "Number is even."
else
    "Number is odd."

abs

Returns the absolute of a number.

Get the absolute value of -1.

Parameter types: (number)

Copy 
abs(-1)

floor

Returns the floor of a number.

Get the floor value for the "unusedVacationDays" column value.

Parameter types: (number)

Copy 
floor(doubleColumn("unusedVacationDays"))

ceiling

Returns the ceiling of a number.

Get the ceiling value for the "totalOvertimeCompensation" column value.

Parameter types: (number)

Copy 
ceiling(doubleColumn("totalOvertimeCompensation"))

round

Rounds a number.

Get the rounded version of the "unusedVacationDays" column value.

Parameter types: (number)

Copy 
round(doubleColumn("unusedVacationDays"))

doubleToDecimal

Rounds a double to a specified number of decimal places.

Round the value "1.2345" to 2 decimal places.

Parameter types: (double, decimalPlaces)

Copy 
doubleToDecimal(1.2345, 2)

Regex functions

Use regular expression rules to manipulate values.

regexCapture

Returns the part of a string that matches the specified regular expression. You can optionally return a defaultValue when the sourceString or regexString is invalid, otherwise returns none.

Get everything before the comma for the "organizationName" column value.

Parameter types: (sourceString, regexString, [defaultValue])

Copy 
regexCapture(column("organizationName"), "(.*),", "Unknown")

regexMatch

Returns a string if it matches the specified regular expression. You can optionally return a defaultValue when the sourceString or regexString is invalid, otherwise returns none.

Get the "location" column value if it contains zero or more alphabetical characters.

Parameter types: (sourceString, regexString, [defaultValue])

Copy 
regexMatch(column("location"), "[a-zA-Z]*", none)

regexSubstitute

Searches and replaces using regular expressions.

Remove "EID" from the start of the "EmployeeID" column value.

Parameter types: (sourceString, regexString, replaceString)

Copy 
regexSubstitute(column("EmployeeID"), "^EID", "")