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.

    Copy
    Example: Returns none if column A is missing.
    column("A",true)
    Copy
    Example: 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, [isOptional])

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

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

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

dateColumn

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

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

  • If isOptional is true and the column doesn't exist in the source file, the rule evaluates and treats the missing column as if it were none.

    Copy
    Example: Returns none if column A is missing.
    column("A",true)
    Copy
    Example: 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, [isOptional])

Copy 
dateColumn("Birthdate")

doubleColumn

Returns the value of a double column.

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

  • If isOptional is true and the column doesn't exist in the source file, the rule evaluates and treats the missing column as if it were none.

    Copy
    Example: Returns none if column A is missing.
    column("A",true)
    Copy
    Example: 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, [isOptional])

Copy 
doubleColumn("Salary")

intColumn

Returns the value of a integer column.

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

  • If isOptional is true and the column doesn't exist in the source file, the rule evaluates and treats the missing column as if it were none.

    Copy
    Example: Returns none if column A is missing.
    column("A",true)
    Copy
    Example: 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, [isOptional])

Copy 
intColumn("Performance")

booleanColumn

Returns the value of a Boolean column.

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

  • If isOptional is true and the column doesn't exist in the source file, the rule evaluates and treats the missing column as if it were none.

    Copy
    Example: Returns none if column A is missing.
    column("A",true)
    Copy
    Example: 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, [isOptional])

Copy 
booleanColumn("isFullTime")

jsonColumn

Returns the value from a JSON column.

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

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

  • If isOptional is true and the column doesn't exist in the source file, the rule evaluates and treats the missing column as if it were none.

    Copy
    Example: Returns none if column A is missing.
    column("A",true)
    Copy
    Example: 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, [isOptional])

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

doubleJsonColumn

Returns a double value from a JSON column.

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

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

  • If isOptional is true and the column doesn't exist in the source file, the rule evaluates and treats the missing column as if it were none.

    Copy
    Example: Returns none if column A is missing.
    column("A",true)
    Copy
    Example: 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, [isOptional])

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

intJsonColumn

Returns an integer value from a JSON column.

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

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

  • If isOptional is true and the column doesn't exist in the source file, the rule evaluates and treats the missing column as if it were none.

    Copy
    Example: Returns none if column A is missing.
    column("A",true)
    Copy
    Example: 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, [isOptional])

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

booleanJsonColumn

Returns a Boolean value from a JSON column.

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

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

  • If isOptional is true and the column doesn't exist in the source file, the rule evaluates and treats the missing column as if it were none.

    Copy
    Example: Returns none if column A is missing.
    column("A",true)
    Copy
    Example: 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.

Examples

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

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

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

dateJsonColumn

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

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

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

  • If isOptional is true and the column doesn't exist in the source file, the rule evaluates and treats the missing column as if it were none.

    Copy
    Example: Returns none if column A is missing.
    column("A",true)
    Copy
    Example: 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, [isOptional])

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

jsonPathColumn

Returns a value described by a JSONPath.

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

  • If isOptional is true and the column doesn't exist in the source file, the rule evaluates and treats the missing column as if it were none.

    Copy
    Example: Returns none if column A is missing.
    column("A",true)
    Copy
    Example: 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, [isOptional])

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

intJsonPathColumn

Returns a integer value described by a JSONPath.

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

  • If isOptional is true and the column doesn't exist in the source file, the rule evaluates and treats the missing column as if it were none.

    Copy
    Example: Returns none if column A is missing.
    column("A",true)
    Copy
    Example: 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, [isOptional])

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

doubleJsonPathColumn

Returns a double value described by a JSONPath.

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

  • If isOptional is true and the column doesn't exist in the source file, the rule evaluates and treats the missing column as if it were none.

    Copy
    Example: Returns none if column A is missing.
    column("A",true)
    Copy
    Example: 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, [isOptional])

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

booleanJsonPathColumn

Returns a Boolean value described by a JSONPath.

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

  • If isOptional is true and the column doesn't exist in the source file, the rule evaluates and treats the missing column as if it were none.

    Copy
    Example: Returns none if column A is missing.
    column("A",true)
    Copy
    Example: 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, [isOptional])

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

dateJsonPathColumn

Returns a date value described by a JSONPath.

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

  • If isOptional is true and the column doesn't exist in the source file, the rule evaluates and treats the missing column as if it were none.

    Copy
    Example: Returns none if column A is missing.
    column("A",true)
    Copy
    Example: 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, [isOptional])

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

delimitedMultiValueColumn

Create separate rows for each delimiter-separated value. The delimiterString is a regex string. To use a reserved regex character, such as $, *, or ^, you must escape the character with \\. To use backslash (\) as the delimiter, you must use \\\\.

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.

    Copy
    Example: Returns none if column A is missing.
    column("A",true)
    Copy
    Example: 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, [isOptional])

Copy 
delimitedMultiValueColumn("Location", ";")

identity

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

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

none

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

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

lookup

Returns a single column value from a file based on matching keys. Requires an existing static map lookup mapping. For more information, see Connect subject member ID property to lookup. You can optionally return a defaultValue as a fallback value.

Note: Only use lookup for primary keys, like EmployeeID or OrgID. For other replacements, use business rule functions such as augmentingMapping or replacementMapping.

Use Employee_ID as a key and replace its value with the corresponding value from the static map lookup mapping for Employee_Id_Lookup_File.

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

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

file

Defines the file in a lookup.

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

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

Parameter types: (fileString)

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

meltColumns

Returns the column names.

Get the column names.

Copy 
meltColumns()

Get the column values.

Copy 
column(meltColumns())

rowNumber

Returns the index of the row being processed.

Get the index of the row currently being processed.

Copy 
rowNumber()

filePath

Returns the path to the input file.

Get the path to the input file.

Copy 
filePath()

fileName

Returns the name of the input file.

Get the name of the input file.

Copy 
fileName()

if

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

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

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

else

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

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

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

Conversion functions

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

toString

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

Convert the 2.2 double to a string value.

Parameter types: (value, [defaultValue])

Copy 
toString(2.2)

toDouble

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

Convert the 2.0 string to a double value.

Parameter types: (string, [defaultValue])

Copy 
toDouble("2.0")

toInt

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

Convert the 2 string to an integer value.

Parameter types: (string, [defaultValue])

Copy 
toInt("2")

toBoolean

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

Convert the True string to a Boolean value.

Parameter types: (string, [defaultValue])

Copy 
toBoolean("True")

toDate

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

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

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

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

intToDouble

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

Convert the integer value of 2 to a double value.

Parameter types: (integer, [defaultValue])

Copy 
intToDouble(2)

doubleToInt

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

Convert the 2.0 double value to an integer value.

Parameter types: (double, [defaultValue])

Copy 
doubleToInt(2.0)

dateToString

Converts a date to a string in the specified format.

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

Parameter types: (dateObject, dateFormat)

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

dateToInt

Converts a date to an integer.

Convert the EffectiveDate value to an integer value.

Parameter types: (dateObject)

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

utcEpochToDate

Converts an epoch timestamp to a date.

Convert an epoch timestamp to a date.

Parameter types: (epochInMillisecondsString)

Copy 
utcEpochToDate("1712095588000")

Rule functions

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

keep

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

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

Parameter types: (column, collection)

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

replace

Replaces values that match members in a collection. 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])

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

skipNone

Skips extracted none values. If the formula result is none and if the attribute allows null values and doesn't have a default value, the rule doesn't create a profile change event for the attribute and doesn't change the attribute value.

Note: To find out if an attribute allows null values and has a default value:

  1. In a project, on the navigation bar, click Model > Analytic Objects.
  2. Select the analytic object associated with the attribute.
  3. In the analytic object, click Default Values.
  4. Find the attribute in the list and then check its settings for Allow null and Default value.
  5. Optional: If the attribute is shared with multiple analytic objects, repeat steps 2 to 4 for all shared analytic objects.

If you can't access Default Values, contact your administrator.

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

Parameter types: (column)

Copy 
skipNone(intColumn("Salary"))

takeNotEmpty

Accepts a collection of columns and returns the first column value that isn't a none value. You can optionally return a defaultValue when all the columns return none, otherwise 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])

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

keepSuccessOnly

Skip the incorrect or empty cell value if the default value is not none. If the formula result is none, the rule doesn't create a profile change event for the attribute and doesn't change the attribute value.

Note: Empty strings ("") are not nulls. To trigger keepSuccessOnly with empty strings, you can add logic in your extraction formula to handle empty strings.

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

Parameter types: (column)

Copy 
keepSuccessOnly(intColumn("Salary"))

Don't update the attribute if column A has no value. If column A has a value, set the attribute to column A's value.

Copy 
keepSuccessOnly(if (column("A") = "") none else column("A"))

Math operators

Use math operators to perform mathematical calculations.

+

Adds numbers.

Get totalCompensation by adding the baseSalary and bonus column values.

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

-

Subtracts numbers.

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

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

*

Multiplies numbers.

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

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

/

Divides numbers.

Get overtimeHours by dividing totalOvertimeCompensation and the overtime hourlyRate.

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

Comparison operators

Use comparison operators to determine how two values compare.

=

Compares two values and determines whether they are equal.

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

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

!=

Compares two values and determines whether they are not equal.

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

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

>

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

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

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

<

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

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

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

>=

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

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

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

<=

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

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

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

Logical operators

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

and

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

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

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

&&

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

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

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

or

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

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

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

||

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

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

Copy 
if ((column("country") = "Canada") || (column("country") = "United States") 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.

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

!

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

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

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

inSet

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

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

Parameter types: (column, values)

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

Date functions

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

date

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

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

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

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

dateToUTC

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

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

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

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

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

dateFormat

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

Note: Date formats are based on the Joda-Time specification.

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

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

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

shiftMillis

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

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

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

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

shiftSeconds

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

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

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

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

shiftMinutes

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

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

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

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

shiftHours

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

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

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

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

shiftDays

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

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

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

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

shiftWeeksWithCustomCalendar

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

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

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

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

shiftMonths

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

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

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

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

shiftMonthsWithCustomCalendar

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

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

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

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

shiftYears

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

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

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

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

shiftYearsWithCustomCalendar

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

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

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

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

firstDateInMonth

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

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

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

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

firstDateInMonthWithCustomCalendar

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

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

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

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

firstDateInQuarterWithCustomCalendar

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

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

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

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

firstDateInWeek

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

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

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

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

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

firstDateInWeekWithCustomCalendar

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

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

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

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

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

lastDateInMonth

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

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

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

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

lastDateInWeek

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

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

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

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

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

lastDateInWeekWithCustomCalendar

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

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

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

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

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

lastDateInMonthWithCustomCalendar

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

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

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

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

lastDateInQuarterWithCustomCalendar

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

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

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

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

dateFromCustomCalendar

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

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

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

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

sourceTimestamp

Returns the snapshot time (UTC) for the uploaded file.

Get the snapshot time (UTC) value.

Copy 
sourceTimestamp()

tenantStartDate

Returns the start date for your tenant.

Get the tenant start date.

Copy 
tenantStartDate()

String functions

Use string functions to work with string values.

leftPadding

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

Get the Month column value in a 2-digit format.

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

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

rightPadding

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

Get the Employee_ID column value in a 10-digit format.

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

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

+

Concatenates string values.

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

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

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

concat

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

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

Get the full name using the FirstName and LastName column values separated by a comma.

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

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

sha256

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

Compute the SHA-256 hash for the FirstName column.

Parameter types: (sourceString)

Copy 
sha256(column("FirstName"))

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)

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

Copy 
toLowerCase(column("EmployeeID"))

toUpperCase

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

Get the EmployeeID column value in upper case.

Parameter types: (sourceString, [defaultValue])

Copy 
toUpperCase(column("EmployeeID"))

trimCharactersLeft

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

Get the Employee_ID column value without the EID prefix.

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

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

trimCharactersRight

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

Get the Performance column value without the Rating text.

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

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

stringLeft

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

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

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

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

stringRight

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

Get the EmployeeID without the EID prefix.

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

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

stringDropLeft

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

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

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

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

stringDropRight

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

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

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

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

stringSubstitute

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

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

Get the EmployeeID column value without the EID prefix.

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

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

stringReplace

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

Remove the EID prefix for the EmployeeID column value.

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

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

indexOf

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

Return Lives in Canada if Canada is found in the Location column value.

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

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

lastIndexOf

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

Return Lives in Canada if Canada is found in the Location column value.

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

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

length

Returns the length of a string.

Get the length of the FirstName column value.

Parameter types: (string)

Copy 
length(column("FirstName"))

capitalizeAll

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

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

Parameter types: (string)

Copy 
capitalizeAll(column("FirstName"))

capitalizeFirstChar

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

Capitalize the first character in the FirstName column.

Parameter types: (string)

Copy 
capitalizeFirstChar(column("FirstName"))

Number functions

Use number functions to work with numbers.

min

Returns the lesser of the two values.

Note: Both values must be of the same type.

Get the lesser of the sickDays and personalDays column values.

Parameter types: (number, number)

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

max

Returns the greater of the two values.

Note: Both values must be of the same type.

Get the greater of the sickDays and personalDays column values.

Parameter types: (number, number)

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

mod

Returns the modulus of two values.

Note: Both values must be of the same type.

Determine whether the number 10 is an even number.

Parameter types: (number, number)

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

abs

Returns the absolute of a number.

Get the absolute value of -1.

Parameter types: (number)

Copy 
abs(-1)

floor

Returns the floor of a number.

Get the floor value for the unusedVacationDays column value.

Parameter types: (number)

Copy 
floor(doubleColumn("unusedVacationDays"))

ceiling

Returns the ceiling of a number.

Get the ceiling value for the totalOvertimeCompensation column value.

Parameter types: (number)

Copy 
ceiling(doubleColumn("totalOvertimeCompensation"))

round

Rounds a number.

Get the rounded version of the unusedVacationDays column value.

Parameter types: (number)

Copy 
round(doubleColumn("unusedVacationDays"))

doubleToDecimal

Rounds a double to a specified number of decimal places.

Round the value 1.2345 to 2 decimal places.

Parameter types: (double, decimalPlaces)

Copy 
doubleToDecimal(1.2345, 2)

Regex functions

Use regular expression rules to manipulate values.

regexCapture

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

Get everything before the comma for the organizationName column value.

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

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

regexMatch

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

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

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

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

regexSubstitute

Searches and replaces using regular expressions.

Remove EID from the start of the EmployeeID column value.

Parameter types: (sourceString, regexString, replaceString)

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