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.
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.
Examples
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 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.
Examples
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 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.
Examples
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 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.
Examples
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 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.
Examples
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 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.
Examples
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 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.
Examples
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 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.
Examples
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 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.
Examples
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 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.
Examples
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 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.
Examples
Get the "workReferenceEnumeration" 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 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.
Examples
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 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.
Examples
Get the "previousSalary" 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 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.
Examples
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 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.
Examples
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 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.
Examples
Split "Location" in to separate rows where-ever multiple locations are listed and separated by a semi-colon.
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.
Examples
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.
Examples
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.
Examples
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.00else
Keyword to define the fallback for a conditional statement. This is used with if.
Examples
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.00Conversion 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.
Examples
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 none is returned.
Examples
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 none is returned.
Examples
Convert the "2" string to an integer value.
Parameter types: (string, [defaultValue])
toInt("2")toBoolean
Converts a value to a Boolean. You can optionally return a defaultValue when the conversion fails, otherwise none is returned.
Examples
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 none is returned.
Examples
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 none is returned.
Examples
Convert the integer value of 2 in to a double.
Parameter types: (integer, [defaultValue])
intToDouble(2)doubleToInt
Converts a double to an integer. You can optionally return a defaultValue when the conversion fails, otherwise none is returned.
Examples
Convert the 2.0 double value to an integer.
Parameter types: (double, [defaultValue])
doubleToInt(2.0)dateToString
Converts a date to a string in the specified format.
Examples
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.
Examples
Convert the "EffectiveDate" value to an integer.
Parameter types: (dateObject)
dateToInt(toDate(toString(column("EffectiveDate")), dateFormat("yyyy-MM-dd HH:mm:ss")))utcEpochToDate
Converts an epoch timestamp to a date.
Examples
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.
Examples
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.
Examples
Replace "M" with "Male" and "F" with "Female".
Parameter types: (column, collection)
replace(column("Gender"), { "M" -> "Male", "F" -> "Female" })skipNone
Skips extracted none values.
Examples
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 none is returned.
Examples
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.
Examples
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.
Examples
Get "totalCompensation" by adding the "baseSalary" and "bonus" column values.
totalCompensation := doubleColumn("baseSalary") + doubleColumn("bonus")-
Subtracts numbers.
Examples
Get "baseSalary" by subtracting the "bonus" column value from the "totalCompensation" column value.
baseSalary := doubleColumn("totalCompensation") - doubleColumn("bonus")*
Multiplies numbers.
Examples
Get "overtimeCompensation" by multiplying the "overtimeHours" column value, "hourlyRate" column value and 1.5.
totalOvertimeCompensation := (intColumn("overtimeHours") * doubleColumn("hourlyRate")) * 1.5/
Divides numbers.
Examples
Get "overtimeHours" by dividing "totalOvertimeCompensation" and the overtime hourly rate.
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.
Examples
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.
Examples
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.
Examples
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.
Examples
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.
Examples
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.
Examples
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 &&.
Examples
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.
Examples
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 ||.
Examples
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.
Examples
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") || (column("country") = "Mexico"))
"Lives in North America."
else
"Lives elsewhere."not
Compares two expressions and returns true if not true. This is interchangeable with !.
Examples
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.
Examples
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.
Examples
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.
Examples
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.
Examples
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 Java SimpleDateFormat specification.
Examples
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.
Examples
Get the "HireDate" in "MM/dd/yyyy" format shifted forward by one 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.
Examples
Get the "HireDate" in "MM/dd/yyyy" format shifted forward by one 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.
Examples
Get the "HireDate" in "MM/dd/yyyy" format shifted forward by one 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.
Examples
Get the "HireDate" in "MM/dd/yyyy" format shifted forward by one 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.
Examples
Get the "HireDate" in "MM/dd/yyyy" format shifted forward by one 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.
Examples
Get the "HireDate" in "MM/dd/yyyy" format shifted forward by one 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.
Examples
Get the "HireDate" in "MM/dd/yyyy" format shifted forward by one 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.
Examples
Get the "HireDate" in "MM/dd/yyyy" format shifted forward by one 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.
Examples
Get the "HireDate" in "MM/dd/yyyy" format shifted forward by one 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.
Examples
Get the "HireDate" in "MM/dd/yyyy" format shifted forward by one 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.
Examples
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.
Examples
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.
Examples
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.
Examples
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.
Examples
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.
Examples
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.
Examples
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.
Examples
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.
Examples
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.
Examples
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")customCalendarDayName
Returns the day name. You can optionally return a defaultValue as a fallback value.
Examples
Get the day name for "startDate".
Parameter types: (dateObject, [defaultValue])
customCalendarDayName(dateColumn("startDate"))customCalendarWeekName
Returns the week name. You can optionally return a defaultValue as a fallback value.
Examples
Get the week name for "startDate".
Parameter types: (dateObject, [defaultValue])
customCalendarWeekName(dateColumn("startDate"))customCalendarMonthName
Returns the month name. You can optionally return a defaultValue as a fallback value.
Examples
Get the month name for "startDate".
Parameter types: (dateObject, [defaultValue])
customCalendarMonthName(dateColumn("startDate"))customCalendarQuarterName
Returns the quarter name. You can optionally return a defaultValue as a fallback value.
Examples
Get the month name for "startDate".
Parameter types: (dateObject, [defaultValue])
customCalendarQuarterName(dateColumn("startDate"))customCalendarYearName
Returns the year name. You can optionally return a defaultValue as a fallback value.
Examples
Get the year name for "startDate".
Parameter types: (dateObject, [defaultValue])
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.
Examples
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.
Examples
Get the "Month" column value in a two-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.
Examples
Get the "Employee_ID" column value in a ten-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.
Examples
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.
Examples
Get the full name using the "FirstName" and "LastName" column values separated by ", ".
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.
Examples
Compute the SHA-256 hash for the "FirstName" column.
Parameter types: (sourceString)
sha256(column("FirstName"))toLowerCase
Converts a string to lower case. You can optionally return a defaultValue when the conversion fails, otherwise returns none.
Examples
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.
Examples
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.
Examples
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.
Examples
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. Additionally, you can optionally return a defaultValue when the sourceString is invalid, otherwise returns none.
Examples
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. Additionally, you can optionally return a defaultValue when the sourceString is invalid, otherwise returns none.
Examples
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.
Examples
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.
Examples
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.
Examples
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.
Examples
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.
Examples
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.
Examples
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.
Examples
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.
Examples
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.
Examples
Capitalize the first character in the "FirstName" column.
Parameter types: (string)
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.
Examples
Get the "FirstName" column value prefixed with "My name is: ".
Parameter types: (sourceString, templateString, [defaultValue])
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.
Examples
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.
Examples
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.
Examples
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.
Examples
Get the floor value for the "unusedVacationDays" column value.
Parameter types: (number)
floor(doubleColumn("unusedVacationDays"))ceiling
Returns the ceiling of a number.
Examples
Get the ceiling value for the "totalOvertimeCompensation" column value.
Parameter types: (number)
ceiling(doubleColumn("totalOvertimeCompensation"))round
Rounds a number.
Examples
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.
Examples
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.
Examples
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.
Examples
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)