Visier Business Rule Language (VBRL)
Use the Visier Business Rule Language to transform data in Visier.
Overview
Use the Visier Business Rules Language (VBRL) to transform data in Visier. VBRL formulas are written as a single expression that transform records.
For more information about adding and configuring business rules, see Business Rules.
Basic syntax
Define rules for adding, updating, and deleting data based on specific triggers.
when
Triggers an action.
Examples
Delete "Employee_Start" when the property value is "Inactive".
when currentValueOf(Employee_Status) = "Inactive" and Employment_Start
delete Employment_Startadd
Adds an event.
Examples
Add a hire event with "Growth" as the reason.
when changeIn(title)
add Position_Change(Reason := "NEW")update
Updates property values.
Examples
Update "effectiveDate" for "Compensation_Payout" to shift forward by two days.
when Compensation_Payout
update current(Compensation_Payout)(effectiveDate := shiftDays(dateOf(Compensation_Payout), 2))Update the "Currency_Code" property to "CAD" when the "Currency_Code" property changes.
when changeIn(Currency_Code)
update Currency_Code := "CAD"delete
Deletes events.
Note:
- You can perform multiple deletes by adding delete before each expression where each expression returns a single event.
- At most, each delete can delete one event.
Examples
Delete events where the name is equal to "Test".
when changeIn(name) && currentValueOf(name) = "Test"
delete changeIn(name)Delete all events where the "FirstName" is equal to "Raymond".
Caution: This approach deletes any event that meets the filter criteria specified in the all function, regardless of the filter specified as the trigger for the action. For example, the statement below, when triggered, deletes all events where the "FirstName" is "Raymond" even though the trigger filter looks for events with a "FirstName" of "Julia".
when PayEvent && PayEvent.FirstName = "Julia"
delete all(PayEvent.FirstName = "Raymond")match
Performs actions when values meet the specified criteria.
Examples
Update the "First_Name" property value when the "First_Name" changes and the value matches "Al", "Bob", and "Unknown".
Parameter types: (expression)
when changeIn(First_Name) match(First_Name) {
"Al" -> update First_Name := "Alice",
"Bob" -> update First_Name := "Robert",
"Unknown" -> update Employee_Status := "Inactive"
}effectiveDate
Sets the date for events. Updating effectiveDate for existing events creates a copy of the event with an updated effectiveDate. If you'd like to remove the event with the previous effectiveDate property value, you can use the delete syntax.
Examples
Update the "effectiveDate" for events that change the "First_Name" property to happen one day before the current "effectiveDate" value.
when changeIn(Employee.First_Name)
update Employee.First_Name := currentValueOf(Employee.First_Name), effectiveDate := shiftDays(dateOf(current(changeIn(Employee.First_Name))), 1),
delete current(changeIn(Employee.First_Name))if
Starts a conditional statement.
Examples
Update "First_Name" to "Robert" if there is a change in "First_Name" and the "Employee_ID" is equal to 10001, otherwise update "First_Name" to "Unknown".
if(changeIn(First_Name) and Employee_ID = "10001")
update First_Name := "Robert"
else
update First_Name := "Unknown"else
Provides the default case in a conditional statement.
Examples
Update "First_Name" to "Robert" when there is a change in "First_Name" and the "Employee_ID" is equal to 10001, otherwise update "First_Name" to "Unknown".
if(changeIn(First_Name) and Employee_ID = "10001")
update First_Name := "Robert"
else
update First_Name := "Unknown"forEach
Updates multiple events that occur at the same time.
Examples
Loop through the "Compensation_Payout" events and set "Payout_Type" to "Salary".
forEach Compensation_Payout
update (Compensation_Payout.Payout_Type := "Salary")call
Invokes predefined rule functions.
Examples
Invoke the backfillChanges function.
call backfillChanges(true, true, allPropertiesExcept({ First_Name }))tenantStartDate
Returns the start date for your tenant.
Event functions
Filter events based on occurring changes, event types, property values, and the position of the event in the context of the event stream. For example, adding a new "Promotion" event when the "Title" property changes.
any
Triggers on any event.
Examples
Update "message" when any event occurs.
Note: In cases where you are processing 5 events, this functions adds a ProfileChangeEvent at every unique date for the 5 events.
Caution: Avoid adding redundant events like the example below.
when any()
add message := "A profile change event just happened!"profileChangeEvent
Returns any profile change events.
Examples
Update "name" to "Robert" when a profile and "name" change occurs, and the "name" is "Rob".
when profileChangeEvent() && changeIn(name) && name = "Rob"
update name := "Robert"changeIn
Triggers on events with changes to a specific property.
Examples
Add a "Position_Change" event when the "title" property changes.
Parameter types: (property)
when changeIn(title)
add Position_Change(Reason := "NEW")changeInAny
Triggers on events with changes to any properties in a collection.
Examples
Add a "Position_Change" event when "title" or "isPromoted" changes.
Parameter types: (propertiesCollection)
if(changeInAny({ title, isPromoted }))
add Position_Change(Reason := "NEW")filter
Returns events that meet the specified filter criteria.
Examples
Update the "title" property to the "New_Title" when a "PositionChanged" event occurs and the "Reason" is "New".
Parameter types: (event, filter)
when filter(PositionChanged, PositionChanged.Reason = "NEW")
update title := currentValueOf(PositionChanged.New_Title)andFilter
Combines two filters.
Examples
Add a "Position_Change" event with the reason set to "XFR" when there is a change in the "title" and "department" values.
Parameter types: (filter, filter)
when andFilter(changeIn(title), changeIn(department))
add Position_Change(Reason := "XFR")orFilter
Returns true if either filter is true, otherwise returns false.
Examples
Add a "Position_Change" event with the reason set to "XFR" when there is a change in the "title" or "department" values.
Parameter types: (filter, filter)
when orFilter(changeIn(title), changeIn(department))
add Position_Change(Reason := "XFR")notFilter
Returns any event that does not meet the filter criteria.
Examples
Add a "Position_Change" event with the reason set to "XFR" when "Job_Title" changes and the property value is not "Unknown".
Parameter types: (filter)
when changeIn(Job_Title) && notFilter(Job_Title = "Unknown")
add Position_Change(Reason := "XFR")conceptionEvent
Triggers on conception events.
Examples
Update "message" to "conception event occurred" when a "conceptionEvent" occurs.
when conceptionEvent()
update message := "conception event occurred"terminationEvent
Triggers on termination events.
Examples
Update "message" to "termination event occurred" when a "terminationEvent" occurs.
when terminationEvent()
update message := "termination event occurred"regularEvent
Triggers on regular events.
Examples
Update "message" to "regular event occurred" when a "regularEvent" occurs.
when regularEvent()
update message := "regular event occurred"current
Returns the most recent event that meets the specified filter criteria.
Examples
Update "effectiveDate" for "Compensation_Payout" to shift forward by two days.
Parameter types: (filter)
when Compensation_Payout
update current(Compensation_Payout)(effectiveDate := shiftDays(dateOf(Compensation_Payout), 2))next
Returns the next event that meets the specified filter criteria.
Examples
Update "effectiveDate" for the next "Compensation_Payout" to shift forward by two days.
Parameter types: (filter)
when Compensation_Payout
update next(Compensation_Payout)(effectiveDate := shiftDays(dateOf(Compensation_Payout), 2))previous
Returns the previous event that meets the specified filter criteria.
Examples
Update "effectiveDate" for the previous "Compensation_Payout" to shift forward by two days.
Parameter types: (filter)
when Compensation_Payout
update previous(Compensation_Payout)(effectiveDate := shiftDays(dateOf(Compensation_Payout), 2))first
Returns the first event that meets the specified filter criteria.
Examples
Update "First_Name" to a default value of "Bob" when the "Last_Name" is updated for the first time and the "First_Name" has never been updated.
Parameter types: (filter)
where first(changeIn(Last_Name)) and !atLeastOnce(changeIn(First_Name))
update First_Name := "Bob"last
Returns the latest event that meets the specified filter criteria.
Examples
Set the "isAlexOrRyan" property to 1 when the last "First_Name" value is equal to "Ryan" or "Alex".
Parameter types: (filter)
when changeIn(First_Name) and containedIn(valueOf(First_Name, last(changeIn(First_Name))), {"Ryan", "Alex"})
update Employee.isAlexOrRyan := 1all
Returns all events in the stream that meet the specified filter criteria.
Examples
Delete all events where the "FirstName" is equal to "Raymond".
Parameter types: (filter)
when PayEvent && PayEvent.FirstName = "Julia"
delete all(PayEvent.FirstName = "Raymond")sameDateAs
Returns true when the event time is equal to the event time returned by an event, otherwise returns false.
Examples
Update "First_Day" to 1 when the event date is equal to the event date of the first "Start_Date" event.
Parameter types: (event)
when sameDateAs(first(Start_Date))
update First_Day := 1Value functions
Get property values from specific events.
valueOf
Returns the value of the property based on an event.
Examples
Set the "isAlexOrRyan" property to 1 when the current "First_Name" value is equal to "Ryan" or "Alex".
Parameter types: (property, filter)
when changeIn(First_Name) and containedIn(valueOf(First_Name, current(changeIn(First_Name))), {"Ryan", "Alex"})
update Employee.isAlexOrRyan := 1dateOf
Returns the date associated with an event.
Examples
Set "LastName" to the "FirstName" property value from a day before it first changed.
Parameter types: (filter)
when changeIn(First_Name)
add Last_Name := valueAsOf(First_Name, shiftDays(dateOf(changeIn(First_Name)), -1))currentValueOf
Returns the value of the property from the most recent event. This is shorthand for valueOf(property, current(changeIn(property))).
Examples
Set the "isAlexOrRyan" property to 1 when the current "First_Name" value is equal to "Ryan" or "Alex".
Parameter types: (property)
when changeIn(First_Name) and containedIn(currentValueOf(First_Name), {"Ryan", "Alex"})
update Employee.isAlexOrRyan := 1nextValueOf
Returns the value of the property from the next event. This is shorthand for valueOf(property, next(changeIn(property))).
Examples
Set the "isAlexOrRyan" property to 1 when the next "First_Name" value is equal to "Ryan" or "Alex".
Parameter types: (property)
when changeIn(First_Name) and containedIn(nextValueOf(First_Name), {"Ryan", "Alex"})
update Employee.isAlexOrRyan := 1previousValueOf
Returns the value of the property from the previous event. This is shorthand for valueOf(property, previous(changeIn(property))).
Examples
Add a "Promotion" event when the current "Pay_Level" is greater than the previous "Pay_Level".
Parameter types: (property)
when changeIn(Pay_Level) and (currentValueOf(Pay_Level) > previousValueOf(Pay_Level))
add Promotion(Promotion_Reason := "Pay Level Increase")firstValueOf
Returns the value of the property from the first event. This is shorthand for valueOf(property, first(changeIn(property))).
Examples
Set the "isAlexOrRyan" property to 1 when the first "First_Name" value is equal to "Ryan" or "Alex".
Parameter types: (property)
when changeIn(First_Name) and containedIn(firstValueOf(First_Name), {"Ryan", "Alex"})
update Employee.isAlexOrRyan := 1lastValueOf
Returns the value of the property from the last event. This is shorthand for valueOf(property, last(changeIn(property))).
Examples
Set the "isAlexOrRyan" property to 1 when the last "First_Name" value is equal to "Ryan" or "Alex".
Parameter types: (property)
when changeIn(First_Name) and containedIn(lastValueOf(First_Name), {"Ryan", "Alex"})
update Employee.isAlexOrRyan := 1valueAsOf
Returns the value of a property at a specific date.
Examples
Update "Last_Name" to yesterday's "First_Name" property value.
Parameter types: (property, dateObject)
when changeIn(First_Name)
add Last_Name := valueAsOf(First_Name, shiftDays(dateOf(changeIn(First_Name)), -1))atLeastOnce
Returns true if the property has changed at least once, otherwise returns false.
Examples
Update the "First_Name" to "Bob" when the "Last_Name" is being changed for the first time and the "First_Name" has never changed.
Parameter types: (filter)
where first(changeIn(Last_Name)) and !atLeastOnce(changeIn(First_Name))
update First_Name := "Bob"containedIn
Compares a value to a collection of values and returns true if there is a match.
Examples
Set the "isAlexOrRyan" property to 1 when the "First_Name" value is equal to "Ryan" or "Alex".
Parameter types: (value, collection)
when changeIn(First_Name) and containedIn(currentValueOf(First_Name), {"Ryan", "Alex"})
update Employee.isAlexOrRyan := 1Aggregation functions
Aggregate property values in your business rules. For example, min, max, sum, and average.
minAggregation
Returns the minimum value for property at the point in time when the filter is true. The minimum is reset when resetTrigger is triggered.
Note: Both property and filter should be operating on the same event, and must be functions that work with one event at a time. For example valueOf(Employee.Salary) is an acceptable property, since it is only concerned with the value of the Salary property of a single event. However, valueOf(previous(Employee.Salary)) is not an acceptable property, since it requires multiple events to determine its value.
Examples
Update "MinPay" to the minimum of "PayAmount" at the point in time where "PayType" is equal to "Regular" and reset the minimum amount at the end of the year.
Parameter types: (property, filter, resetTrigger)
when endOf(year())
update MinPay := minAggregation(PayEvent.PayAmount, PayEvent.PayType = "Regular", endOf(year()))maxAggregation
Returns the maximum value for property at the point in time when the filter is true. The maximum is reset when the resetTrigger is triggered.
Note: Both property and filter should be operating on the same event, and must be functions that work with one event at a time. For example valueOf(Employee.Salary) is an acceptable property, since it is only concerned with the value of the Salary property of a single event. However, valueOf(previous(Employee.Salary)) is not an acceptable property, since it requires multiple events to determine its value.
Examples
Update "MaxPay" to the maximum of "PayAmount" at the point in time where "PayType" is equal to "Regular" and reset the maximum amount at the end of the year.
Parameter types: (property, filter, resetTrigger)
when endOf(year())
update MaxPay := maxAggregation(PayEvent.PayAmount, PayEvent.PayType = "Regular", endOf(year()))sumAggregation
Returns the sum value for property at the point in time when the filter is true. The sum is reset when the resetTrigger is triggered.
Note: Both property and filter should be operating on the same event, and must be functions that work with one event at a time. For example valueOf(Employee.Salary) is an acceptable property, since it is only concerned with the value of the Salary property of a single event. However, valueOf(previous(Employee.Salary)) is not an acceptable property, since it requires multiple events to determine its value.
Examples
Update "TotalPay" to the sum of "PayAmount" at the point in time where "PayType" is equal to "Regular" and reset the sum at the end of the year.
Parameter types: (property, filter, resetTrigger)
when endOf(year())
update TotalPay := sumAggregation(PayEvent.PayAmount, PayEvent.PayType = "Regular", endOf(year()))averageAggregation
Returns the average value for property at the point in time when the filter is true. The average is reset when the resetTrigger is triggered.
Note: Both property and filter should be operating on the same event, and must be functions that work with one event at a time. For example valueOf(Employee.Salary) is an acceptable property, since it is only concerned with the value of the Salary property of a single event. However, valueOf(previous(Employee.Salary)) is not an acceptable property, since it requires multiple events to determine its value.
Examples
Update "AveragePay" to the average of "PayAmount" at the point in time where "PayType" is equal to "Regular" and reset the average amount at the end of the year.
Parameter types: (property, filter, resetTrigger)
when endOf(year())
update AveragePay := averageAggregation(PayEvent.PayAmount, PayEvent.PayType = "Regular", endOf(year()))Date functions
Define, format, and shift dates, calculate the difference between dates, and get the start and end of a date interval. For example, day, week, month, and year.
date
Returns a date object.
Examples
Set the "hiredPostCovidShutdown" property to a value of 1 when an employee was hired after March 1, 2020.
Parameter types: (yearNumber, monthNumber, dayNumber)
when Employee.Hire_Date >= date(2020,3,1)
update hiredPostCovidShutdown := 1formatDate
Returns a date string in the specified format.
Examples
Set "date_string" to the "date" value in the "MM-dd-yyyy" format.
Parameter types: (dateFormatString, dateValue)
when changeIn(date)
update date_string := formatDate("MM-dd-yyyy", date)getDay
Returns the day from a date as an integer.
Examples
Get the day from the "Last_Promotion_Date" property.
Parameter types: (dateObject)
when changeIn(Last_Promotion_Date)
update number := getDay(dateOf(changeIn(Last_Promotion_Date)))getMonth
Returns the month from a date as an integer.
Examples
Get the month from the "Last_Promotion_Date" property.
Parameter types: (dateObject)
when changeIn(Last_Promotion_Date)
update number := getMonth(dateOf(changeIn(Last_Promotion_Date)))getYear
Returns the year from a date as an integer.
Examples
Get the year from the "Last_Promotion_Date" property.
Parameter types: (dateObject)
when changeIn(Last_Promotion_Date)
update number := getYear(dateOf(changeIn(Last_Promotion_Date)))getCustomCalendarDay
Returns the day from a date as an integer based on the configured custom calendar.
Examples
Get the day from the "Last_Promotion_Date" property.
Parameter types: (dateObject)
when changeIn(Last_Promotion_Date)
update number := getCustomCalendarDay(dateOf(changeIn(Last_Promotion_Date)))getCustomCalendarMonth
Returns the month from a date as an integer based on the configured custom calendar.
Examples
Get the month from the "Last_Promotion_Date" property.
Parameter types: (dateObject)
when changeIn(Last_Promotion_Date)
update number := getCustomCalendarMonth(dateOf(changeIn(Last_Promotion_Date)))getCustomCalendarYear
Returns the year from a date as an integer based on the configured custom calendar.
Examples
Get the year from the "Last_Promotion_Date" property.
Parameter types: (dateObject)
when changeIn(Last_Promotion_Date)
update number := getCustomCalendarYear(dateOf(changeIn(Last_Promotion_Date)))firstOfMonth
Returns a date value shifted to the first day of calendar month.
Examples
Get the first day of the month for the month that a promotion last occurred.
Parameter types: (dateValue)
firstOfMonth(dateOf(changeIn(Last_Promotion_Date)))firstOfYear
Returns a date value shifted to the first day of calendar year.
Examples
Get the first day of the year for the year that a promotion last occurred.
Parameter types: (dateValue)
firstOfYear(dateOf(changeIn(Last_Promotion_Date)))firstOfCustomCalendarMonth
Returns a date value shifted to the first day of the custom calendar month.
Examples
Get the first day of the month for the month that a promotion last occurred, based on the configured custom calendar.
Parameter types: (dateValue)
firstOfCustomCalendarMonth(dateOf(changeIn(Last_Promotion_Date)))firstOfCustomCalendarYear
Returns a date value shifted to the first day of the custom calendar year.
Examples
Get the first day of the year for the year that a promotion last occurred, based on the configured custom calendar.
Parameter types: (dateValue)
firstOfCustomCalendarYear(dateOf(changeIn(Last_Promotion_Date)))lastOfMonth
Returns a date value shifted to the last day of the month.
Examples
Get the last date of the month for the month that a promotion last occurred.
Parameter types: (dateValue)
lastOfMonth(dateOf(changeIn(Last_Promotion_Date)))lastOfYear
Returns a date value shifted to the last day of the year.
Examples
Get the last date of the year for the year that a promotion last occurred.
Parameter types: (dateValue)
lastOfYear(dateOf(changeIn(Last_Promotion_Date)))lastOfCustomCalendarMonth
Returns a date value shifted to the last day of the custom calendar month.
Examples
Get the last date of the month for the month that a promotion last occurred.
Parameter types: (dateValue)
lastOfCustomCalendarMonth(dateOf(changeIn(Last_Promotion_Date)))lastOfCustomCalendarYear
Returns a date value shifted to the last day of the custom calendar year.
Examples
Get the last date of the year for the year that a promotion last occurred.
Parameter types: (dateValue)
lastOfCustomCalendarYear(dateOf(changeIn(Last_Promotion_Date)))millisBetween
Returns the number of milliseconds between two dates. This function returns a negative value if the first argument is less than the second argument.
Examples
Update "number" to the number of milliseconds between the current date value and the last date value.
Parameter types: (dateValue, dateValue)
when changeIn(date)
update number := millisBetween(date, lastValueOf(date))secondsBetween
Returns the number of seconds between two dates. This function returns a negative value if the first argument is less than the second argument.
Examples
Update "number" to the number of seconds between the current date value and the last date value.
Parameter types: (dateValue, dateValue)
when changeIn(date)
update number := secondsBetween(date, lastValueOf(date))minutesBetween
Returns the number of minutes between two dates. This function returns a negative value if the first argument is less than the second argument.
Examples
Update "number" to the number of minutes between the current date value and the last date value.
Parameter types: (dateValue, dateValue)
when changeIn(date)
update number := minutesBetween(date, lastValueOf(date))hoursBetween
Returns the number of hours between two dates. This function returns a negative value if the first argument is less than the second argument.
Examples
Update "number" to the number of hours between the current date value and the last date value.
Parameter types: (dateValue, dateValue)
when changeIn(date)
update number := hoursBetween(date, lastValueOf(date))daysBetween
Returns the number of days between two dates. This function returns a negative value if the first argument is less than the second argument.
Examples
Update "number" to the number of days between the current date value and the last date value.
Parameter types: (dateValue, dateValue)
when changeIn(date)
update number := daysBetween(date, lastValueOf(date))monthsBetween
Returns the number of months between two dates. This function returns a negative value if the first argument is less than the second argument.
Examples
Update "number" to the number of months between the current date value and the last date value.
Parameter types: (dateValue, dateValue)
when changeIn(date)
update number := monthsBetween(date, lastValueOf(date))yearsBetween
Returns the number of years between two dates. This function returns a negative value if the first argument is less than the second argument.
Examples
Update "number" to the number of years between the current date value and the last date value.
Parameter types: (dateValue, dateValue)
when changeIn(date)
update number := yearsBetween(date, lastValueOf(date))shiftMillis
Returns a date shifted by a number of milliseconds. You can specify a positive or negative value for the number of milliseconds.
Examples
Shift the most recent Employee_Exit event forward by one millisecond when there is an Employment_Start event within 1 day of an Employee_Exit event.
Parameter types: (dateObject, numberOfMilliseconds)
when Employment_Start and (daysBetween(dateOf(Employee_Exit), dateOf(Employment_Start)) = 1)
update current(Employee_Exit)(effectiveDate := shiftMillis(dateOf(Employee_Exit), 1))shiftSeconds
Returns a date shifted by a number of seconds. You can specify a positive or negative value for the number of seconds.
Examples
Shift the most recent "Employee_Exit" event forward by one second when there is an "Employment_Start" event within 1 day of an "Employee_Exit" event.
Parameter types: (dateObject, numberOfSeconds)
when Employment_Start and (daysBetween(dateOf(Employee_Exit), dateOf(Employment_Start)) = 1)
update current(Employee_Exit)(effectiveDate := shiftSeconds(dateOf(Employee_Exit), 1))shiftMinutes
Returns a date shifted by a number of minutes. You can specify a positive or negative value for the number of minutes.
Examples
Shift the most recent "Employee_Exit" event forward by one minute when there is an "Employment_Start" event within 1 day of an "Employee_Exit" event.
Parameter types: (dateObject, numberOfMinutes)
when Employment_Start and (daysBetween(dateOf(Employee_Exit), dateOf(Employment_Start)) = 1)
update current(Employee_Exit)(effectiveDate := shiftMinutes(dateOf(Employee_Exit), 1))shiftHours
Returns a date shifted by a number of hours. You can specify a positive or negative value for the number of hours.
Examples
Shift the most recent "Employee_Exit" event forward by one hour when there is an "Employment_Start" event within 1 day of an "Employee_Exit" event.
Parameter types: (dateObject, numberOfHours)
when Employment_Start and (daysBetween(dateOf(Employee_Exit), dateOf(Employment_Start)) = 1)
update current(Employee_Exit)(effectiveDate := shiftHours(dateOf(Employee_Exit), 1))shiftDays
Returns a date shifted by a number of days. You can specify a positive or negative value for the number of days.
Examples
Shift the most recent "Employee_Exit" event forward by one day when there is an "Employment_Start" event within 1 day of an "Employee_Exit" event.
Parameter types: (dateObject, numberOfDays)
when Employment_Start and (daysBetween(dateOf(Employee_Exit), dateOf(Employment_Start)) = 1)
update current(Employee_Exit)(effectiveDate := shiftDays(dateOf(Employee_Exit), 1))shiftMonths
Returns a date shifted by a number of months. You can specify a positive or negative value for the number of months.
Examples
Shift the most recent "Employee_Exit" event forward by one month when there is an "Employment_Start" event within 1 day of an "Employee_Exit" event.
Parameter types: (dateObject, numberOfMonths)
when Employment_Start and (daysBetween(dateOf(Employee_Exit), dateOf(Employment_Start)) = 1)
update current(Employee_Exit)(effectiveDate := shiftMonths(dateOf(Employee_Exit), 1))shiftYears
Returns a date shifted by a number of years. You can specify a positive or negative value for the number of years.
Examples
Shift the most recent "Employee_Exit" event forward by one year when there is an "Employment_Start" event within 1 day of an "Employee_Exit" event.
Parameter types: (dateObject, numberOfYears)
when Employment_Start and (daysBetween(dateOf(Employee_Exit), dateOf(Employment_Start)) = 1)
update current(Employee_Exit)(effectiveDate := shiftYears(dateOf(Employee_Exit), 1))startOf
Returns the start of the specified date or time interval.
Examples
Update "Total_Compensation" to the sum of "PayAmount" and reset the sum at the start of each year.
Parameter types: (dateTimeInterval)
update Total_Compensation := sumAggregation(PayAmount, PayAmount, startOf(year()))endOf
Returns the end of the specified date or time interval.
Examples
Update "Total_Compensation" to the sum of "PayAmount" and reset the sum at the end of each year.
Parameter types: (dateTimeInterval)
update Total_Compensation := sumAggregation(PayAmount, PayAmount, endOf(year()))day
Returns a day time interval when using startOf or endOf.
Examples
Update "Total_Compensation" to the sum of "PayAmount" and reset the sum at the start of each day.
update Total_Compensation := sumAggregation(PayAmount, PayAmount, startOf(day()))week
Returns a week time interval when using startOf or endOf.
Examples
Update "Total_Compensation" to the sum of "PayAmount" and reset the sum at the start of each week.
update Total_Compensation := sumAggregation(PayAmount, PayAmount, startOf(week()))month
Returns a month time interval when using startOf or endOf.
Examples
Update "Total_Compensation" to the sum of "PayAmount" and reset the sum at the start of each month.
update Total_Compensation := sumAggregation(PayAmount, PayAmount, startOf(month()))year
Returns a year time interval when using startOf or endOf.
Examples
Update "Total_Compensation" to the sum of "PayAmount" and reset the sum at the start of each year.
update Total_Compensation := sumAggregation(PayAmount, PayAmount, startOf(year()))Comparison operators
Compare values.
=
Compares two values and determines whether they are equal.
Examples
Update "Gender" to "Male" if the value is "M", otherwise update to "Other".
if (Gender = "M")
update Gender := "Male"
else
update Gender := "Other"!=
Compares two values and determines whether they are not equal.
Examples
Update "Gender" to "Other" if the value is not "M", otherwise update to "Male".
if (Gender != "M")
update Gender := "Other"
else
update Gender := "Male">
Compares two values and determines whether the left value is greater than the right value.
Examples
Update "Status" to "Full Time" if "FTE_Factor" is greater than 9, otherwise update to "Part Time".
if (FTE_Factor > 9)
update Status := "Full Time"
else
update Status := "Part Time"<
Compares two values and determines whether the left value is less than the right value.
Examples
Update "Status" to "Part Time" if "FTE_Factor" is less than 9, otherwise update to "Full Time".
if (FTE_Factor < 9)
update Status := "Part Time"
else
update Status := "Full Time">=
Compares two values and determines whether the left value is greater than or equal to the right value.
Examples
Update "Status" to "Full Time" if "FTE_Factor" is equal to or greater than 10, otherwise update to "Part Time".
if (FTE_Factor >= 10)
update Status := "Full Time"
else
update Status := "Part Time"<=
Compares two values and determines whether the left value is less than or equal to the right value.
Examples
Update "Status" to "Part Time" if "FTE_Factor" is equal to or less than 9, otherwise update to "Full Time".
if (FTE_Factor <= 9)
update Status := "Part Time"
else
update Status := "Full Time"Logical operators
Compare expressions and return a Boolean value.
and
Compares two expressions and returns true if both are true. This is interchangeable with &&.
Examples
Add a "Promotion" event when the current "Pay_Level" is greater than the previous "Pay_Level".
when changeIn(Pay_Level) and (currentValueOf(Pay_Level) > previousValueOf(Pay_Level))
add Promotion(Promotion_Reason := "Pay Level Increase")&&
Compares two expressions and returns true if both are true. This is interchangeable with and.
Examples
Add a "Promotion" event when the current "Pay_Level" is greater than the previous "Pay_Level".
when changeIn(Pay_Level) && (currentValueOf(Pay_Level) > previousValueOf(Pay_Level))
add Promotion(Promotion_Reason := "Pay Level Increase")or
Compares two expressions and returns true if either is true. This is interchangeable with ||.
Examples
Add a "Promotion" event when the current "Pay_Level" is greater than the previous "Pay_Level" or "Title" changes.
when (changeIn(Pay_Level) and (currentValueOf(Pay_Level) > previousValueOf(Pay_Level))) or changeIn(Title)
add Promotion(Promotion_Reason := "Pay Level Increase")||
Compares two expressions and returns true if either is true. This is interchangeable with or.
Examples
Add a "Promotion" event when the current "Pay_Level" is greater than the previous "Pay_Level" or "Title" changes.
when (changeIn(Pay_Level) and (currentValueOf(Pay_Level) > previousValueOf(Pay_Level))) || changeIn(Title)
add Promotion(Promotion_Reason := "Pay Level Increase")not
Compares two expressions and returns true if not true. This is interchangeable with !.
Examples
Update Location to "Outside NA" if the "country" column value is not "Canada", "United States" or "Mexico", otherwise update "Location" to "North America".
if not (Location = "Canada" or Location = "USA" or Location = "Mexico")
update Location := "Outside NA"
else
update Location := "North America"!
Compares two expressions and returns true if not true. This is interchangeable with not.
Examples
Update Location to "Outside NA" if the "country" column value is not "Canada", "United States" or "Mexico", otherwise update "Location" to "North America".
if !(Location = "Canada" or Location = "USA" or Location = "Mexico")
update Location := "Outside NA"
else
update Location := "North America"String functions
Alters strings by concatenating, changing capitalization, and more.
+
Concatenates strings.
Examples
Update "Full_Name" to a value that concatenates the "First_Name" and "Last_Name" property values separated by a space.
when changeIn(First_Name) || changeIn(Last_Name)
update Full_Name := First_Name + " " + Last_Namesha256
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: (string)
if(changeIn(FirstName))
update FirstName := sha256(FirstName)toLowerCase
Converts a string to lower case.
Examples
Update the "EmployeeID" column value in lower case.
Parameter types: (string)
when changeIn(Employee_ID)
update Employee_ID := toLowerCase(Employee_ID)toUpperCase
Converts a string to upper case.
Examples
Get the "EmployeeID" column value in upper case.
Parameter types: (string)
when changeIn(Employee_ID)
update Employee_ID := toUpperCase(Employee_ID)trimWhitespace
Removes white space from a string.
Examples
Remove white space from the Employee_ID property value.
Parameter types: (string)
when changeIn(Employee_ID)
update Employee_ID := trimWhitespace(EmployeeID)stringLeft
Returns a subset of characters from the left-side of a string.
Examples
Get the year from the string value "2024/01/15".
Parameter types: (sourceString, length)
stringLeft("2024/01/15", 4)stringRight
Returns a subset of characters from the right-side of a string.
Examples
Get the "EmployeeID" without the "EID" prefix.
Parameter types: (sourceString, length)
stringRight(EmployeeID, (length(EmployeeID) - 3))stringDropLeft
Removes characters from the start of a string.
Examples
Remove preceding "EID" from the "Employee_ID" property value.
Parameter types: (sourceString, length)
when changeIn(Employee_ID)
update Employee_ID := stringDropLeft(Employee_ID, 3)stringDropRight
Removes characters from the end of a string.
Examples
Remove "EID" from the end of the "Employee_ID" property value.
Parameter types: (sourceString, length)
when changeIn(Employee_ID)
update Employee_ID := stringDropRight(Employee_ID, 3)stringSubstitute
Searches and replaces in a string.
Examples
Get the "EmployeeID" column value without the "EID" prefix.
Parameter types: (sourceString, searchString, replaceString)
stringSubstitute(column("EmployeeID"), "EID", "")stringReplace
Replaces a substring.
Examples
Produce a value that consists of the first letter of the "First_Name" followed by the "Last Name".
Parameter types: (sourceString, startIndex, numberOfCharacters, replaceString)
stringReplace(FirstName, 1, 10, LastName)regexCapture
Returns the first capture group if there is a match. Otherwise, returns none.
Examples
Update "Employee_ID" to the employee ID without the preceding "EID" characters.
Parameter types: (sourceString, regexString)
when changeIn(Employee_ID)
update Employee_ID := regexCapture(Employee_ID, "([0-9]{10})$")regexMatch
Returns the part of a string value that matches the specified regular expression. Otherwise, returns none.
Examples
Update "isManager" to a value of 1 when there is a change in "title" and the "title" includes "manager".
Parameter types: (sourceString, regexString)
when changeIn(title) && length(regexMatch(title, "(.*?)manager")) > 0
update isManager := 1regexSubstitute
Searches and replaces using regular expressions.
Examples
Update "name" to "Not Valid" when "name" changes and "name" is equal to "Test".
Parameter types: (sourceString, regexString, replacementString)
when changeIn(name) && name = "Test"
update name := regexSubstitute(name, "(.*?)Test", "Not Valid")indexOf
Returns the starting index of the first occurrence of searchString found in valueString. Optionally, you can specify a starting index if you'd like to start the search at a position greater than 0.
Examples
Update "nameIncludesRob" to a value of 1 when the "name" includes "Rob".
Parameter types: (valueString, searchString, [startIndex])
when changeIn(name) && indexOf(name, "Rob", 0) >= 0
update nameIncludesRob := 1lastIndexOf
Returns the starting index of the last occurrence of searchString found in valueString. Optionally, you can specify a starting index if you'd like to start the search at a position greater than 0.
Examples
Update "nameIncludesRob" to a value of 1 when the "name" includes "Rob".
Parameter types: (valueString, searchString, [startIndex])
when changeIn(name) && lastIndexOf(name, "Rob") >= 0
update nameIncludesRob := 1length
Returns the length of a string as an integer.
Examples
Update "nameExists" to 1 when the length of "name" is greater than 0.
Parameter types: (string)
when changeIn(name) && length(name) > 0
update nameExists := 1capitalizeAll
Converts the first character of all words in a string to upper case and other characters to lower case.
Examples
Capitalize the "FirstName" property value.
Parameter types: (string)
if(changeIn(FirstName))
update FirstName := capitalizeAll(FirstName)capitalizeFirstChar
Converts the first character in a string to upper case. This does not modify other characters.
Examples
Capitalize the first character for the "FirstName" property value.
Parameter types: (string)
if(changeIn(FirstName))
update FirstName := capitalizeFirstChar(FirstName)Math functions
Get the minimum, maximum, modulus, absolute, floor, ceiling, and rounded numbers.
min
Returns the lesser of the two values. Both values should be of the same type.
Examples
Update the "number" property value to itself or 100, whichever is less.
Parameter types: (number, number)
when changeIn(number)
update number := min(number, 100.0)max
Returns the greater of two values. Both values should be of the same type.
Examples
Update the "number" property value to itself or 100, whichever is greater.
Parameter types: (number, number)
when changeIn(number)
update number := max(number, 100.0)mod
Returns the modulus of the two values.
Examples
Update the "number" property value to the modulus of 9 and 2.
Parameter types: (number, number)
when changeIn(name)
update number := mod(9, 2)abs
Returns the absolute value.
Examples
Add a new "Hire" event with a "Hire_Reason" value that is positive when the "name" property changes.
Parameter types: (number)
when changeIn(name)
add Hire(Hire_Reason := abs(-1))floor
Returns the floor for a number.
Examples
Update "Employee_Salary" to the floor equivalent.
Parameter types: (number)
when changeIn(Employee_Salary)
update Employee_Salary := floor(Employee_Salary)ceiling
Returns the ceiling of a number.
Examples
Update "totalOvertimeCompensation" to the ceiling equivalent.
Parameter types: (number)
if(changeIn(totalOvertimeCompensation))
update totalOvertimeCompensation := ceiling(totalOvertimeCompensation)round
Returns the rounded value.
Examples
Update "Total_Compensation" to the rounded equivalent.
Parameter types: (number)
when changeIn(Total_Compensation)
update Total_Compensation := round(Total_Compensation)Predefined rule functions
Special functions that perform operations that go beyond what other functions can do.
groupAndPick
Returns a single event out of a set of events and deletes all other events in the set. This function is useful for removing duplicate events, resolving inconsistencies between data sources, and handling multiple changes happening on the same date.
Related functions and examples
groupAndPick
Example
Groups pay events by "Amount" where "Amount" is greater than 0, pick the latest event, and delete all other events in each group.
Parameter types: (picker, groupBy, groupFilter)
call groupAndPick(pickLatest(PayEvent), PayEvent.Amount, PayEvent.Amount > 0)pickEarliest
Returns the earliest event.
Note: If two events have the same date, then "Record_Period_ID" and "Sequence_Number" are used to return the event.
Example
Picks the earliest "PayEvent" where "Type" is equal to "Salary" for each month where the event takes place between "January 15, 2024" and "January 14, 2025".
Parameter types: (eventFunction, pickFilter, interval, startDate, endDate)
call groupAndPick(pickEarliest(PayEvent, PayEvent.Type = "Salary", months(1), date(2024,1,15), date(2025,1,15)))pickLatest
Returns the latest event.
Note: If two events have the same date, then "Record_Period_ID" and "Sequence_Number" are used to return the event.
Example
Picks the latest "PayEvent" where "Type" is equal to "Salary" for each month where the event takes place between "January 15, 2024" and "January 14, 2025".
Parameter types: (eventFunction, pickFilter, interval, startDate, endDate)
call groupAndPick(pickLatest(PayEvent, PayEvent.Type = "Salary", months(1), date(2024,1,15), date(2025,1,15)))pickByMin
Returns the event with the minimum value in a group of events. The minimum resets when the resetTrigger is triggered.
Example
Picks the "PayEvent" with the minimum "PayAmount".
Parameter types: (property, filter, resetTrigger)
call groupAndPick(pickByMin(PayEvent.PayAmount, PayEvent, last(PayEvent)))pickByMax
Returns the event with the maximum value in a group of events. The maximum resets when the resetTrigger is triggered.
Example
Picks the "PayEvent" with the maximum "PayAmount".
Parameter types: (property, filter, resetTrigger)
call groupAndPick(pickByMax(PayEvent.PayAmount, PayEvent, last(PayEvent)))concatenate
Combines a group of property changes in to a single property change. This function affects any events that meet the eventFilter criteria and updates the property specified as the resultProperty to the concatenated value.
Example
Combine any groups of "First_Name" property changes in to a single property change that is equal to the a concatenation of all "First_Name" values.
Parameter types: (eventFilter, resultProperty, delimiterString)
call groupAndPick(concatenate(First_Name, First_Name, " "))contentOf
Groups events based on content.
Example
Remove the duplicate "PayEvents" within the same date by grouping those events based on their content and dropping all but the last one.
Parameter types: (event)
call groupAndPick(pickLatest(PayEvent), contentOf(PayEvent))days
Groups events that happen within the specified number of days of the initial member of a group.
Example
Groups pay events that happen within two days of the initial member and pick the earliest one.
Parameter types: (number)
call groupAndPick(pickEarliest(PayEvent, days(2)))weeks
Groups events that happen within the specified number of weeks of the initial member of a group.
Example
Groups pay events that happen within two weeks of the initial member and pick the earliest one.
Parameter types: (number)
call groupAndPick(pickEarliest(PayEvent, weeks(2)))months
Groups events that happen within the specified number of months of the initial member of a group.
Example
Groups pay events that happen within two months of the initial member and pick the earliest one.
Parameter types: (number)
call groupAndPick(pickEarliest(PayEvent, months(2)))years
Groups events that happen within the specified number of years of the initial member of a group.
Example
Groups pay events that happen within two years of the initial member and pick the earliest one.
Parameter types: (number)
call groupAndPick(pickEarliest(PayEvent, years(2)))addValidRanges
Adds a valid range for profile change events that meet the following criteria:
- The property does not have a valid range defined.
- There is another profile change event with the same date and a valid range defined.
This function updates invalid ranges with a valid range pulled from a profile change event that shares the same date and has a valid range defined.
By default, the criteria for an invalid range is a value that equals to none. You can override this behavior by specifying a filter and adding your own criteria for what should be considered to be an invalid range.
Examples
Add a valid range for "Employee.Employment_Status".
Parameter types: (propertiesCollection, [filter])
call addValidRanges({ Employee.Employment_Status })augmentingMapping
Augments a mapping using a lookup mapping object.
Related functions and examples
augmentingMapping
Example
Map the "Employee.Pay_Level_Compensation_Maximum" property to the "CompMax" value and the "Employee.Pay_Level_Compensation_Minimum" value to "CompMin" using the "pay_levels_post_stg" lookup mapping object.
Parameter types: (mappingObjectNameString, mappingKeyProperty, mappedProperties, [ifUnmapped, filter, ifOutputExist, ignoreIfNoValue])
call augmentingMapping(
"pay_levels_post_stg",
Employee.JobPayLevelCode,
{
Employee.Pay_Level_Compensation_Maximum -> "CompMax",
Employee.Pay_Level_Compensation_Minimum -> "CompMin"
},
useDefault(),
dateOf(changeIn(Employee.JobPayLevelCode)) >= date(2018,1,1),
augmentAnyways(),
false
)augmentingMultipleMappings
Augments multiple mappings using lookup mapping objects. Similar to augmentingMapping except that it accepts a target group name instead of a lookup mapping object name as it's first argument.
Example
Map the "Employee.Pay_Level_Compensation_Maximum" property to the "CompMax" value and the "Employee.Pay_Level_Compensation_Minimum" value to "CompMin" using the "pay_levels_post_stg" lookup mapping object.
Parameter types: (targetGroupNameString, mappingKeyProperty, mappedProperties, [ifUnmapped, filter, ifOutputExist, ignoreIfNoValue])
call augmentingMultipleMappings(
"compensation_target_group",
Employee.JobPayLevelCode,
{
Employee.Pay_Level_Compensation_Maximum -> "CompMax",
Employee.Pay_Level_Compensation_Minimum -> "CompMin"
},
useDefault(),
dateOf(changeIn(Employee.JobPayLevelCode)) >= date(2018,1,1),
augmentAnyways(),
false
)ignore
Does nothing when a lookup key doesn't exist in the lookup stream. This is the default behavior when using augmentingMapping. Alternatively, you can use useDefault to insert an event with a default value when a lookup key doesn't exist in the lookup stream.
Example
Map the "Employee.Pay_Level_Compensation_Maximum" property to the "CompMax" value and the "Employee.Pay_Level_Compensation_Minimum" value to "CompMin" using the "pay_levels_post_stg" lookup mapping object.
call augmentingMapping(
"pay_levels_post_stg",
Employee.JobPayLevelCode,
{
Employee.Pay_Level_Compensation_Maximum -> "CompMax",
Employee.Pay_Level_Compensation_Minimum -> "CompMin"
},
ignore(),
dateOf(changeIn(Employee.JobPayLevelCode)) >= date(2018,1,1),
augmentAnyways(),
false
)useDefault
Inserts an event with a default value when a lookup key doesn't exist in the lookup stream when using augmentingMapping. Alternatively, you can use ignore to do nothing when a lookup key doesn't exist in the lookup stream.
Example
Map the "Employee.Pay_Level_Compensation_Maximum" property to the "CompMax" value and the "Employee.Pay_Level_Compensation_Minimum" value to "CompMin" using the "pay_levels_post_stg" lookup mapping object.
call augmentingMapping(
"pay_levels_post_stg",
Employee.JobPayLevelCode,
{
Employee.Pay_Level_Compensation_Maximum -> "CompMax",
Employee.Pay_Level_Compensation_Minimum -> "CompMin"
},
useDefault(),
dateOf(changeIn(Employee.JobPayLevelCode)) >= date(2018,1,1),
augmentAnyways(),
false
)augmentAnyways
Augments the event stream with lookup events even when property values exist. This is the default behavior when using augmentingMapping. Alternatively, you can augment the event stream only when property values do not exist by using noAugmentIfValueExist.
Example
Map the "Employee.Pay_Level_Compensation_Maximum" property to the "CompMax" value and the "Employee.Pay_Level_Compensation_Minimum" value to "CompMin" using the "pay_levels_post_stg" lookup mapping object.
call augmentingMapping(
"pay_levels_post_stg",
Employee.JobPayLevelCode,
{
Employee.Pay_Level_Compensation_Maximum -> "CompMax",
Employee.Pay_Level_Compensation_Minimum -> "CompMin"
},
ignore(),
dateOf(changeIn(Employee.JobPayLevelCode)) >= date(2018,1,1),
augmentAnyways(),
false
)noAugmentIfValueExist
Augments the event stream with lookup events when property values do not exist. Alternatively, you can augment the event stream when property values exist by using augmentAnyways.
Example
Map the "Employee.Pay_Level_Compensation_Maximum" property to the "CompMax" value and the "Employee.Pay_Level_Compensation_Minimum" value to "CompMin" using the "pay_levels_post_stg" lookup mapping object.
call augmentingMapping(
"pay_levels_post_stg",
Employee.JobPayLevelCode,
{
Employee.Pay_Level_Compensation_Maximum -> "CompMax",
Employee.Pay_Level_Compensation_Minimum -> "CompMin"
},
ignore(),
dateOf(changeIn(Employee.JobPayLevelCode)) >= date(2018,1,1),
noAugmentIfValueExist(),
false
)backfillChanges
Updates the initial value assignment of the specified properties to happen at some initial date.
Related functions and examples
backfillChanges
Example
Update the "Event Time" for all properties except "First Name" to the earliest property change that took place.
Parameter types: (skipNoneBoolean, backfillPrimaryStreamOnlyBoolean, propertiesCollection, startDateObject)
call backfillChanges(true, true, allPropertiesExcept({ First_Name }))allPropertiesExcept
Excludes properties from being updated.
Example
Update the "Event Time" for all properties except "First Name" to the earliest property change that took place.
Parameter types: (propertiesCollection)
call backfillChanges(true, true, allPropertiesExcept({ First_Name }))changeDates
Adds an event with a specified value and event date derived from the value of another date property. By default, this function creates one event per date property specified in the datePropertiesCollection argument, based on the latest event record. You can change this behavior and create an event for every date record by setting a true value for the optional changeAllBoolean argument.
Examples
Add an event for the "RequisitionStatus" property with a value of "Created" at the "createdDate" point in time, "Approved" at the "approvedDate" point in time, and "Closed" as the "closedDate" point in time.
Parameter types: (property, datePropertiesCollection, [changeAllBoolean])
call changeDates(RequisitionStatus, {
createdDate -> "Created",
approvedDate -> "Approved",
closedDate -> "Closed"
})consolidateRegularEvents
Consolidates events with the same date into one event.
Related functions and examples
consolidateRegularEvents
Example
Consolidate "PayEvent" events based on matching "PayType" and use the average of the existing "Amount" values as the new "Amount" value.
Parameter types: (targetObject, groupingProperty, resolutionFunctionsCollection, filter)
call consolidateRegularEvents(
PayEvent,
PayEvent.PayType,
{ PayEvent.Amount -> average() },
PayEvent.PayType != "Bonus"
)ignoreAttribute
Ignores values as a resolution strategy.
Example
Consolidate "PayEvent" events based on matching "PayType" and use the average of the existing "Amount" values as the new "Amount" value.
Parameter types: (targetObject, groupingProperty, resolutionFunctionsCollection, filter)
call consolidateRegularEvents(
PayEvent,
PayEvent.PayType,
{
PayEvent.Amount -> average(),
PayEvent.CurrencyCode -> ignoreAttribute()
},
PayEvent.PayType != "Bonus"
)sum
Returns the sum of values.
Example
Consolidate "PayEvent" events based on matching "PayType" and use the sum of the existing "Amount" values as the new "Amount" value.
call consolidateRegularEvents(
PayEvent,
PayEvent.PayType,
{ PayEvent.Amount -> sum() }
)average
Returns the average of values.
Example
Consolidate "PayEvent" events based on matching "PayType" and use the average of the existing "Amount" values as the new "Amount" value.
call consolidateRegularEvents(
PayEvent,
PayEvent.PayType,
{ PayEvent.Amount -> average() }
)min
Returns the minimum of values.
Example
Consolidate "PayEvent" events based on matching "PayType" and use the minimum of the existing "Amount" values as the new "Amount" value.
call consolidateRegularEvents(
PayEvent,
PayEvent.PayType,
{ PayEvent.Amount -> min() }
)max
Returns the maximum of values.
Example
Consolidate "PayEvent" events based on matching "PayType" and use the maximum of the existing "Amount" values as the new "Amount" value.
call consolidateRegularEvents(
PayEvent,
PayEvent.PayType,
{ PayEvent.Amount -> max() }
)ensureAllAreEqual
Ensures all the values are equal. If the values are not equal, the data loading job throws an exception and fails.
Example
Consolidate "PayEvent" events based on matching "PayType" and use the average of the existing "Amount" values as the new "Amount" value.
call consolidateRegularEvents(
PayEvent,
PayEvent.PayType,
{ PayEvent.Amount -> ensureAllAreEqual() }
)createSubjectMember
Inserts a new subjectID and related properties.
Note: You cannot use a subjectID that already exists in your data.
Examples
Insert a new "Root" parent-child dimension member.
Parameter types: (idProperty, id, [otherPropertyAssignments, creationDate])
call createSubjectMember(Organization_ID, "-1", {Organization_Name -> "Acme Corp"})createProfilesForCTEvents
Creates conception and termination events from actual conception and termination events. This is useful for dealing with non-snapshot data which is necessary for the CT System Rule.
Examples
Create conception and termination events for Employee events where the "Contract_Type" is "Contingent Worker".
Parameter types: (filter)
call createProfilesForCTEvents(Employee.Contract_Type = "Contingent Worker")dropProfileIfCondition
Deletes all events for the current subject where the condition provided is true.
Examples
Delete all events for subjects where the "First_Name" property changes, the event occurs before February 1, 2023 and the "Last_Name" or "AnotherProperty" does not change.
Parameter types: (condition, [cancelTrigger, endDate])
call dropProfileIfCondition(changeIn(First_Name), changeInAny({Last_Name, AnotherProperty}), date(2023, 2, 1))eventsToPropertyChanges
Converts the values of properties on events into property changes in the main subject profile. These changes take place at the same time as the event used to generate them.
Examples
Convert "Employee.FTE_Factor" to profile events for "FTE" events and delete the original event once the profile events have been created.
Parameter types: (propertiesCollection, [removeOriginalEvent, filter])
call eventsToPropertyChanges({Employee.FTE_Factor}, true, FTE)filterSelfReference
Removes Profile Change Events where the provided reference property creates a self-reference loop.
Examples
Remove an employee's reference to themselves as manager.
Parameter types: (referenceProperty)
call filterSelfReference(Parent_Cost_Center_ID)ignorePropertyChangesDuringInactivity
Updates isolated inactive profiles with a new sourceID so that subsequent rules can ignore them.
Examples
Ignore property changes when NonActiveEmployee property marks an employee as inactive.
Parameter types: (inactiveProperty, [valueForInactive, valueForActive, filter])
call ignorePropertyChangesDuringInactivity(NonActiveEmployee)moveEventsToMatchFoundEventDate
Matches an event based on conditional criteria and updates the event date for all previous events to the event date of the matched event.
Examples
Update the event date for all events that occur prior to the event where the "name" equals "Rob".
Parameter types: (matchCriteria, [cancelCriteria])
call moveEventsToMatchFoundEventDate(name = "Rob")moveToStartOfDay
Updates the event date to the start of the day for all events.
replacementMapping
Replaces the values of a property with values in the specified target mapping file. This function overrides the current value with the value in the lookup file where there is a matching key.
Note: If the lookup fails, the property value is set to none.
Examples
Update "New_Location_Value" to the "Employee.Location.Location_3" value pulled from the "Location_Lookup" mapping.
Parameter types: (mappingName, mappingKey, targetColumn, [filter, useDefault, ignoreIfNoValue])
call replacementMapping("Location_Lookup", Employee.Location.Location_3, "New_Location_Value")replacementMultipleMapping
Replaces the values of a property with values in multiple mapping files. This function overrides the current value with the value in the lookup file where there is a matching key.
Examples
Update "New_Location_Value" to the "Employee.Location.Location_3" value pulled from mapping files that share the "Location_Lookup" type description.
Parameter types: (typeDescription, mappingKey, targetColumn, [filter, useDefault, ignoreIfNoValue])
call replacementMultipleMapping("Location_Lookup", Employee.Location.Location_3, "New_Location_Value")resolveMixedTemporalAndTransTables
Removes duplicates and pushes the event date to the earliest date for the specified properties.
Note: When used, this rule must be the first business rule.
Examples
Remove duplicates and push the event date to the earliest date for "First_Name", "Last_Name", "Company", "EMail", and "Contact_Title".
Parameter types: (filter, [startDate])
call resolveMixedTemporalAndTransTables(changeInAny({First_Name, Last_Name, Company, EMail, Contact_Title}))stripOthersValueFromTimestamp
Removes all additional information except event date from the timestamp.
Examples
Remove all additional information except event date from the timestamp.
call stripOthersValueFromTimestamp()Conversion functions
Convert 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 values.
toString
Converts a value to a string.
toDouble
Converts a value to a double.
toInt
Converts a value to an integer.