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 evaluates and treats the missing column as if it were none.
CopyExample: Uses column A if it exists and has a value, otherwise uses column B if it exists and has a value. Returns none if columns A and B are missing.takeNotEmpty({column("A",true), column("B",true)})
- If isOptional is false and the column doesn't exist in the source file, the rule does nothing and results in a profile change event that sets the attribute to none.

Get the value of the FirstName column.
Parameter types: (columnName
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.
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 evaluates and treats the missing column as if it were none.
CopyExample: Uses column A if it exists and has a value, otherwise uses column B if it exists and has a value. Returns none if columns A and B are missing.takeNotEmpty({column("A",true), column("B",true)})
- If isOptional is false and the column doesn't exist in the source file, the rule does nothing and results in a profile change event that sets the attribute to none.

Get Birthdate value in the format defined in the source column settings.
Parameter types: (columnName
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 evaluates and treats the missing column as if it were none.
CopyExample: Uses column A if it exists and has a value, otherwise uses column B if it exists and has a value. Returns none if columns A and B are missing.takeNotEmpty({column("A",true), column("B",true)})
- If isOptional is false and the column doesn't exist in the source file, the rule does nothing and results in a profile change event that sets the attribute to none.

Get Salary as a double value.
Parameter types: (columnName
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 evaluates and treats the missing column as if it were none.
CopyExample: Uses column A if it exists and has a value, otherwise uses column B if it exists and has a value. Returns none if columns A and B are missing.takeNotEmpty({column("A",true), column("B",true)})
- If isOptional is false and the column doesn't exist in the source file, the rule does nothing and results in a profile change event that sets the attribute to none.

Get Performance as an integer value.
Parameter types: (columnName
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 evaluates and treats the missing column as if it were none.
CopyExample: Uses column A if it exists and has a value, otherwise uses column B if it exists and has a value. Returns none if columns A and B are missing.takeNotEmpty({column("A",true), column("B",true)})
- If isOptional is false and the column doesn't exist in the source file, the rule does nothing and results in a profile change event that sets the attribute to none.

Get isFullTime as a Boolean value.
Parameter types: (columnName
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 evaluates and treats the missing column as if it were none.
CopyExample: Uses column A if it exists and has a value, otherwise uses column B if it exists and has a value. Returns none if columns A and B are missing.takeNotEmpty({column("A",true), column("B",true)})
- If isOptional is false and the column doesn't exist in the source file, the rule does nothing and results in a profile change event that sets the attribute to none.

Get the name value from the JSON object stored in the Employee Information column.
Parameter types: (columnName, jsonKeyName
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 evaluates and treats the missing column as if it were none.
CopyExample: Uses column A if it exists and has a value, otherwise uses column B if it exists and has a value. Returns none if columns A and B are missing.takeNotEmpty({column("A",true), column("B",true)})
- If isOptional is false and the column doesn't exist in the source file, the rule does nothing and results in a profile change event that sets the attribute to none.

Get the Salary value from the JSON object stored in the Employee Information column.
Parameter types: (columnName, jsonKeyName
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 evaluates and treats the missing column as if it were none.
CopyExample: Uses column A if it exists and has a value, otherwise uses column B if it exists and has a value. Returns none if columns A and B are missing.takeNotEmpty({column("A",true), column("B",true)})
- If isOptional is false and the column doesn't exist in the source file, the rule does nothing and results in a profile change event that sets the attribute to none.

Get the Performance value from the JSON object stored in the Employee Information column.
Parameter types: (columnName, jsonKeyName
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 evaluates and treats the missing column as if it were none.
CopyExample: Uses column A if it exists and has a value, otherwise uses column B if it exists and has a value. Returns none if columns A and B are missing.takeNotEmpty({column("A",true), column("B",true)})
- If isOptional is false and the column doesn't exist in the source file, the rule does nothing and results in a profile change event that sets the attribute to none.

Get the isFullTime value from the JSON object stored in the Employee Information column.
Parameter types: (columnName, jsonKeyName
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 evaluates and treats the missing column as if it were none.
CopyExample: Uses column A if it exists and has a value, otherwise uses column B if it exists and has a value. Returns none if columns A and B are missing.takeNotEmpty({column("A",true), column("B",true)})
- If isOptional is false and the column doesn't exist in the source file, the rule does nothing and results in a profile change event that sets the attribute to none.

Get the HiredDate value from the JSON object stored in the Employee Information column.
Parameter types: (columnName, jsonKeyName
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 evaluates and treats the missing column as if it were none.
CopyExample: Uses column A if it exists and has a value, otherwise uses column B if it exists and has a value. Returns none if columns A and B are missing.takeNotEmpty({column("A",true), column("B",true)})
- If isOptional is false and the column doesn't exist in the source file, the rule does nothing and results in a profile change event that sets the attribute to none.

Get the workerReferenceEnumeration value from the workdayId column value.
Parameter types: (columnName, jsonPathString
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 evaluates and treats the missing column as if it were none.
CopyExample: Uses column A if it exists and has a value, otherwise uses column B if it exists and has a value. Returns none if columns A and B are missing.takeNotEmpty({column("A",true), column("B",true)})
- If isOptional is false and the column doesn't exist in the source file, the rule does nothing and results in a profile change event that sets the attribute to none.

Get the id value from the workdayId column value.
Parameter types: (columnName, jsonPathString
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 evaluates and treats the missing column as if it were none.
CopyExample: Uses column A if it exists and has a value, otherwise uses column B if it exists and has a value. Returns none if columns A and B are missing.takeNotEmpty({column("A",true), column("B",true)})
- If isOptional is false and the column doesn't exist in the source file, the rule does nothing and results in a profile change event that sets the attribute to none.

Get the previousCompensation value from the salaryHistory column value.
Parameter types: (columnName, jsonPathString
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 evaluates and treats the missing column as if it were none.
CopyExample: Uses column A if it exists and has a value, otherwise uses column B if it exists and has a value. Returns none if columns A and B are missing.takeNotEmpty({column("A",true), column("B",true)})
- If isOptional is false and the column doesn't exist in the source file, the rule does nothing and results in a profile change event that sets the attribute to none.

Determine whether the employee working remotely as of their most recent location.
Parameter types: (columnName, jsonPathString
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 evaluates and treats the missing column as if it were none.
CopyExample: Uses column A if it exists and has a value, otherwise uses column B if it exists and has a value. Returns none if columns A and B are missing.takeNotEmpty({column("A",true), column("B",true)})
- If isOptional is false and the column doesn't exist in the source file, the rule does nothing and results in a profile change event that sets the attribute to none.

Get the completion date for most recent education that the applicant has completed.
Parameter types: (columnName, jsonPathString
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 evaluates and treats the missing column as if it were none.
CopyExample: Uses column A if it exists and has a value, otherwise uses column B if it exists and has a value. Returns none if columns A and B are missing.takeNotEmpty({column("A",true), column("B",true)})
- If isOptional is false and the column doesn't exist in the source file, the rule does nothing and results in a profile change event that sets the attribute to none.

Split Location into separate rows wherever multiple locations are listed and separated by a semicolon.
Parameter types: (columnName, delimiterString
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])
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)
lookup(column("Employee_ID"), file("Employee_Id_Lookup_File"))
meltColumns
Returns the column names.
rowNumber
Returns the index of the row being processed.
filePath
Returns the path to the input file.
fileName
Returns the name of the input file.
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.
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.
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 returns none.

Convert the 2.2 double to a string value.
Parameter types: (value, [defaultValue])
toString(2.2)
toDouble
Converts a value to a double. You can optionally return a defaultValue when the conversion fails, otherwise returns none.

Convert the 2.0 string to a double value.
Parameter types: (string, [defaultValue])
toDouble("2.0")
toInt
Converts a value to an integer. You can optionally return a defaultValue when the conversion fails, otherwise returns none.
toBoolean
Converts a value to a Boolean. You can optionally return a defaultValue when the conversion fails, otherwise returns none.

Convert the True string to a Boolean value.
Parameter types: (string, [defaultValue])
toBoolean("True")
toDate
Converts a value to a date. You can optionally return a defaultValue when the conversion fails, otherwise returns none.

Convert the HireDate column to a date value in the MM/dd/yyyy format.
Parameter types: (string, dateFormat, [defaultValue])
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 returns none.

Convert the integer value of 2 to a double value.
Parameter types: (integer, [defaultValue])
intToDouble(2)
doubleToInt
Converts a double to an integer. You can optionally return a defaultValue when the conversion fails, otherwise returns none.

Convert the 2.0 double value to an integer value.
Parameter types: (double, [defaultValue])
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)
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 value.
Parameter types: (dateObject)
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)
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)
keep(column("Gender"), { "M", "F" })
replace
Replaces values that match members in a collection. You can optionally return a defaultValue when the column value is not in the collection, otherwise returns none.

Replace M with Male and F with Female.
Parameter types: (column, collection, [defaultValue])
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)
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 returns none.

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])
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)
keepSuccessOnly(intColumn("Salary"))
Math operators
Use math operators to perform mathematical calculations.
+
Adds numbers.

Get totalCompensation by adding the baseSalary and bonus column values.
totalCompensation := doubleColumn("baseSalary") + doubleColumn("bonus")
-
Subtracts numbers.

Get baseSalary by subtracting the bonus column value from the totalCompensation column value.
baseSalary := doubleColumn("totalCompensation") - doubleColumn("bonus")
*
Multiplies numbers.

Get overtimeCompensation by multiplying the overtimeHours column value, hourlyRate column value, and 1.5.
totalOvertimeCompensation := (intColumn("overtimeHours") * doubleColumn("hourlyRate")) * 1.5
/
Divides numbers.

Get overtimeHours by dividing totalOvertimeCompensation and the overtime hourlyRate.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
if ((column("country") = "Canada") || (column("country") = "United States") or (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.
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.
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)
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])
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])
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 Joda-Time specification.

Get the Birthdate column value as a date object in MM/dd/yyyy format.
Parameter types: (dateString, dateFormat, [defaultValue])
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 1 millisecond, otherwise return 01/09/2023.
Parameter types: (dateString, dateFormat, offset, [defaultValue])
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 1 second, otherwise return 01/09/2023.
Parameter types: (dateString, dateFormat, offset, [defaultValue])
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 1 minute, otherwise return 01/09/2023.
Parameter types: (dateString, dateFormat, offset, [defaultValue])
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 1 hour, otherwise return 01/09/2023.
Parameter types: (dateString, dateFormat, offset, [defaultValue])
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 1 day, otherwise return 01/09/2023.
Parameter types: (dateString, dateFormat, offset, [defaultValue])
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 1 week, otherwise return 01/09/2023.
Parameter types: (dateString, dateFormat, offset, [defaultValue])
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 1 month, otherwise return 01/09/2023.
Parameter types: (dateString, dateFormat, offset, [defaultValue])
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 1 month, otherwise return 01/09/2023.
Parameter types: (dateString, dateFormat, offset, [defaultValue])
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 1 year, otherwise return 01/09/2023.
Parameter types: (dateString, dateFormat, offset, [defaultValue])
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 1 year, otherwise return 01/09/2023.
Parameter types: (dateString, dateFormat, offset, [defaultValue])
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])
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])
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])
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])
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])
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])
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])
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])
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])
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])
lastDateInQuarterWithCustomCalendar(column("HireDate"), dateFormat("MM/dd/yyyy"), "01/09/2023")
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])
dateFromCustomCalendar("FY20", "Q4", "P11")
sourceTimestamp
Returns the snapshot time (UTC) for the uploaded file.
tenantStartDate
Returns the start date for your tenant.
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 2-digit format.
Parameter types: (sourceString, paddingString, lengthInteger, [defaultValue])
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 10-digit format.
Parameter types: (sourceString, paddingString, lengthInteger, [defaultValue])
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.
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 a comma.
Parameter types: (collection, delimiterString, [defaultValue])
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)
sha256(column("FirstName"))
unidecode
Removes accents from a string.

Applies unidecode to the FirstName column to remove accent marks. Hélène is returned as Helene.
Parameter types: (string)
unidecode(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])
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])
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])
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])
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. 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])
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. 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])
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])
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])
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])
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])
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])
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])
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)
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)
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)
capitalizeFirstChar(column("FirstName"))
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)
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)
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)
if(mod(10, 2) = 0)
"Number is even."
else
"Number is odd."
abs
Returns the absolute of a number.
floor
Returns the floor of a number.

Get the floor value for the unusedVacationDays column value.
Parameter types: (number)
floor(doubleColumn("unusedVacationDays"))
ceiling
Returns the ceiling of a number.

Get the ceiling value for the totalOvertimeCompensation column value.
Parameter types: (number)
ceiling(doubleColumn("totalOvertimeCompensation"))
round
Rounds a number.

Get the rounded version of the unusedVacationDays column value.
Parameter types: (number)
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)
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])
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])
regexMatch(column("Location"), "[a-zA-Z]*", none)