This article provides details related to the available transformation rules when creating a transformation definition or doing a transformation override in a moov configuration.
Append
Appends the entered text to the selected input field
Concatenate
Combines multiple fields into one output field, separated by an optional character or string
Example: selects 2 input fields and separates them by a space.
Format Phone Number With Dashes
Ensures a phone number is formatted in ###-###-#### or ###-###-#### x####
Format SSN With Dashes
Ensures a social security number is formatted in ###-##-####
Format Zip Code Either ##### or #####-####
Formats input to zip code format.
Result is ##### if input length is less than 5 or #####-#### if more than 5.
Left padding with 0s occur where needed.
Input Field Empty
Outputs the value in the True field if the input field is empty, else outputs the value in the False field
Input Passthrough - allows the original value to be returned if the field is not empty
Break if condition met - prevents processing any subsequent rules if input field is empty
Example - if the Employee Number field is empty, return "N/A" and allow the original value to passthrough if the field is not empty.
LEFT Each Delimited Part
Extract characters from the left side of each delimited part
Delimiter - a single character that separates the input field string
LEFT instructions (comma separated) - the amount of characters to return from the string to the left of the delimiter (use * for all characters)
Example - we wish to only have the first 5 characters of a zip code returned, our delimiter is a hyphen (-) and we take all characters from the left.
Minimum Length - Padding Left
Fills the space to the left of the input field up to a minimum length with the chosen fill character
Fill character - the character used to fill the remaining space
Minimum Length - the amount of characters to fill with the fill character entered
Example - we fill the field with 0s to the left of the Employee Number where the minimum length of the field is 8 characters. If the Employee Number is less than 8 characters, the remaining characters are 0 filled.
Input Field Employee Number = 77506
Output Field results in 00077506
Nested IF-THEN-ELSE
See the Transformation Rule - Nested IF-THEN-ELSE article.
Numeric Only
Remove all non-numeric characters
Prepend
Prepends the entered text to the value
Quote Field
Puts the value of the field in double quotes
Record Count Per Unique Key Value
Calculates the cardinal number of records with the same value(s) in the selected input fields
Record Number Per Unique Key Value
Calculates the ordinal number amongst records with the same value(s) in the selected input fields
Reorder Delimited Part
Reorder the values within the delimited text.
For example, if we have an employee name of: Smith, John
The following would set the delimiter as a comma (,) and reorder the string so the output would be: John Smith
Replace/Remove By RegEx
Replaces characters in the string based on the regex pattern provided with the replacement value provided. Define the regex pattern to use in the ‘Regex’ field and enter a string value in the ‘With’ field. Leave ‘With’ empty if you only want to remove a string.
See our How to apply Replace/Remove transformation rules article.
Replace/Remove exact value using a list
Replaces the value with another value if an exact match is found or removes it based on the defined replacement group values.
See our How to apply Replace/Remove transformation rules article.
Replace/Remove First Occurrence of String
Replaces the first occurrence of a string in a string sequence with another string or removes it.
See our How to apply Replace/Remove transformation rules article.
Replace/Remove Last Occurrence of String
Replaces the last occurrence of a string in a string sequence with another string or removes it.
See our How to apply Replace/Remove transformation rules article.
Replace/Remove String
Replaces every occurrence of a string in a string sequence with another string or removes it.
See our How to apply Replace/Remove transformation rules article.
Replace/Remove value using a list
Replaces every occurrence of the value with another value or removes it based on the defined replacement group values.
See our How to apply Replace/Remove transformation rules article.
Split By Character
Splits the selected input field at the specified character into a list of values and returns the value at specified index of the list (index is zero based).
In this example, we have selected the employee's full name (Smith, John) as the input field and entered a comma as the split character. Entering a 1 into the Index field will take the 2nd index of the list (since the index is zero based) and return "John" in the output field.
Split By RegEx
Splits the input field using a regular expression into to a list of values and returns the value at specified index of the list (index is zero based).
In this example, we have selected the employee's full name which will include the separator "-*-" (i.e. Smith-*-John). By using a regular expression and entering in a 1 into the index field, the result of this rule will return the employee's first name "John".
Substring
Returns a substring of the input field at a starting position to a defined length or if left empty to end of string.
Take First 'X' Characters
Takes the first "x" number of characters in a string.
Text Based Hours And Minutes To Decimal Hours
Take text in a format such as "10 hrs 30 mins" and convert to 10.5.
To Lowercase
Converts all characters to lowercase
To Proper Case
Converts the string to proper case (except for words that are entirely in uppercase, which are considered to be acronyms).
To Title Case
Converts the string to title case (except for words that are entirely in uppercase, which are considered to be acronyms).
To Uppercase
Converts all characters to uppercase
Try Format As Date
Tries to format an input value into a date value. Allowed format characters are y for year, M for month and d for day, and are case sensitive.
If the input field value cannot be formatted as a Date, then the original value in the input field will be returned.
Try Format As Decimal
Tries to format an input value into a decimal value.
If the input field value cannot be formatted as a Decimal, then the original value in the input field will be returned.
Try Format As Integer
Tries to format an input value into a decimal value.
If the input field value cannot be formatted as a Decimal, then the original value in the input field will be returned.
Use first non-null value
The first non-null value will be used. This rule will look in all records in the source file and use the first non-null value found. If no value is found for all records, a blank value will be returned.
Source field:
| Location |
| Grantsville |
| West Chester |
| West Chester |
| Louisville |
For example, if we apply this rule to the Location field above, the value "Grantsville" will be returned for all records:
Output field:
| Location |
| Grantsville |
| Grantsville |
| Grantsville |
| Grantsville |
Use first non-null value if empty
If a null/empty value is found, the first non-null value will be utilized.
Source field:
| Location |
| Grantsville |
| West Chester |
| Louisville |
For example, if we apply this rule to the Location field above, the value "Grantsville" will be returned for the field that is null/empty:
Output field:
| Location |
| Grantsville |
| West Chester |
| Grantsville |
| Louisville |
Use last non-null value if empty
If a null/empty value is found, the last non-null value will be utilized.
Source field:
| Location |
| Grantsville |
| West Chester |
| Louisville |
For example, if we apply this rule to the Location field above, the value "West Chester" will be returned for the field that is null/empty.
Output field:
| Location |
| Grantsville |
| West Chester |
| West Chester |
| Louisville |
Absolute Value
Returns the absolute value of the numeric input selected.
Add
Adds the numeric value (the addend) to the current value of the selected input field.
Add Values
Adds the selected input field numeric values. Multiple values can be selected from the Input Fields dropdown.
Concatenate
Combines multiple fields into one output field, separated by an optional character or string
Convert To Currency
Converts the numeric value to its currency representation.
Example: 15.56 is converted to $15.56
Convert To EBCDIC Signed
Converts a number into its EBCDIC (Extended Binary Code Decimal Interchange Code) representation.
This removes all non-numeric characters. If a + or - sign is present it will be represented by changing the last digit of the number.
Divide
Divides the numeric value by the specified value (divisor).
Divide Values
Divides the selected input field numeric values. Multiple values can be selected from the Input Fields dropdown.
If Equal
Creates an output string based on a numeric comparison of input value.
Define comparable value in 'Compare value' and output value in 'Value'. If condition not met, output will be empty or input value.
In this example, if the selected input value is equal to 100, the value "Value is equal" is returned in the output field. If the value is not equal to 100, then the original input value is passed through by checking the "input passthrough" option. If this option is not selected and the value is not equal, then an empty value would be returned.
If Greater
Creates an output string based on a numeric comparison of input value.
Define comparable value in 'Compare value' and output value in 'Value'. If condition not met, output will be empty or input value.
In this example, if the selected input value is greater than 100, the value "Value greater than 100" is returned in the output field. If the value is less than 100, then the original input value is passed through by checking the "input passthrough" option. If this option is not selected and the value is not greater than, then an empty value would be returned.
If Lesser
Creates an output string based on a numeric comparison of input value.
Define comparable value in 'Compare value' and output value in 'Value'. If condition not met, output will be empty or input value.
In this example, if the selected input value is less than 100, the value "Value less than 100" is returned in the output field. If the value is greater than 100, then the original input value is passed through by checking the "input passthrough" option. If this option is not selected and the value is not less than, then an empty value would be returned.
Input Field Empty
Outputs the value in the True field if the input field is empty, else outputs the value in the False field
Input Passthrough - allows the original value to be returned if the field is not empty
Break if condition met - prevents processing any subsequent rules if input field is empty
In this example, if the Employer Match field is empty, return 0.00, else return the original value in the Employer Match field.
Max Value
Finds the highest numeric value in a given set of numeric values.
Min Value
Finds the lowest numeric value in a given set of numeric values.
Multiply
Multiplies the numeric value by the specified value (multiplier).
Multiply Values
Multiplies the selected input field numeric values. Multiple values can be selected from the Input Fields dropdown.
Nested IF-THEN-ELSE
See the Transformation Rule - Nested IF-THEN-ELSE article.
Numeric Only
Remove all non-numeric characters
Quote Field
Puts the value of the field in double quotes
Record Count Per Unique Key Value
Calculates the cardinal number of records with the same value(s) in the selected input fields
Record Number Per Unique Key Value
Calculates the ordinal number amongst records with the same value(s) in the selected input fields
Remove Currency Symbol
Removes the currency symbol from a numeric field
Remove Separators
Removes decimal and thousands separators from numeric value. Output includes zeros to the right of decimal
Remove Separators Leave Decimal Point
Removes thousands separators from numeric value but leaves the decimal point.
Round Number
Rounds number to nearest number with decimals specified. Empty Decimals means no decimals.
Round Number - Force Decimals
Rounds number to nearest number with decimals specified. Empty Decimals means no decimals. Decimals are displayed even if '0'.
Signed Number
Add sign in front of number if missing.
Subtract
Subtracts the numeric value (the subtrahend) from the current value.
Subtract Values
Subtracts the selected input field numeric values. Multiple values can be selected from the Input Fields dropdown.
Sum Values
Calculates the sum of the selected numeric values. Multiple values can be selected from the Input Fields dropdown.
Try Format As Date
Tries to format an input value into a date value. Allowed format characters are y for year, M for month and d for day, and are case sensitive.
If the input field value cannot be formatted as a Date, then the original value in the input field will be returned.
Try Format As Decimal
Tries to format an input value into a decimal value.
If the input field value cannot be formatted as a Decimal, then the original value in the input field will be returned.
Try Format As Integer
Tries to format an input value into a decimal value.
If the input field value cannot be formatted as a Decimal, then the original value in the input field will be returned.
Use first non-null value
The first non-null value will be used. This rule will look in all records in the source file and use the first non-null value found. If no value is found for all records, a blank value will be returned.
Source field:
| Employer Match |
| 125.52 |
| 12.52 |
| 0.00 |
| 0.00 |
For example, if we apply this rule to the Employer Match field above, the value 125.52 will be returned for all records.
Output field:
| Employer Match |
| 125.52 |
| 125.52 |
| 125.52 |
| 125.52 |
Use first non-null value if empty
If a null/empty value is found, the first non-null value will be utilized.
Source field:
| Employer Match |
| 125.52 |
| 12.52 |
| 0.00 |
For example, if we apply this rule to the Employer Match field above, the value 125.52 will be returned for the field that is null/empty.
Output field:
| Employer Match |
| 125.52 |
| 12.52 |
| 125.52 |
| 0.00 |
Use last non-null value if empty
If a null/empty value is found, the last non-null value will be utilized.
Source field:
| Employer Match |
| 125.52 |
| 12.52 |
| 0.00 |
For example, if we apply this rule to the Employer Match field above, the value 12.52 will be returned for the field that is null/empty.
Output field:
| Employer Match |
| 125.52 |
| 12.52 |
| 12.52 |
| 0.00 |
Add 'X' Days to Date
Adds 'x' days to the input field date.
Add 'X' Days to Date a if b is earlier than c
Adds 'x' days to input date a (first field) if b (second field) is earlier than c (third field).
This rule will only be available if you have selected three Date data type input fields
Concatenate
Concatenates multiple fields into one output field, separated by an optional character or string. Empty fields will be ignored.
Convert DateTime to ISO 8601 Format
Converts the DateTime to ISO 8601 format including applying a time zone offset.
This example will convert the Hire Date field (formatted as M/d/yyyy) to the ISO 8601 format yyyy-MM-ddTHH:mm:ssK with an EST offset.
Current Date if Empty
Outputs the date of transformation execution, if input field is empty. Requires a date format to be selected for the desired output.
Date for Date of the Week
For the supplied date, return the date for the day of the week selected.
In this example, we have selected the DOB field as our input field, the week starts on a Sunday, and we want to return the date for Wednesday of the DOB week.
If our input field DOB is 2025-01-20, then our output date will be 2025-01-222.
Date Format Conversion
Converts the input date format to a different output date format.
In this example, a Hire Date of 01/15/2023 would be converted to 01-15-2023.
Date In Past
Checks if the input field Date is in the past and returns a value based on what is in the True and False fields .
Input Passthrough - allows the original value to be returned if the field is not empty
Break if condition met - prevents processing any subsequent rules if input field is empty
Date Not In Past
Checks if the input field Date is not in the past and returns a value based on what is in the True and False fields .
Input Passthrough - allows the original value to be returned if the field is not empty
Break if condition met - prevents processing any subsequent rules if input field is empty
First Day Of Month
Returns the first day of the month from the selected input field.
Input Field Empty
Outputs the value in the True field if the input field is empty, else outputs the value in the False field
Input Passthrough - allows the original value to be returned if the field is not empty
Break if condition met - prevents processing any subsequent rules if input field is empty
Last Day Of Month
Returns the last day of the month from the selected input field.
Most Past Date
Finds the most past date in a given set of dates. The Date pattern of first selected input date will be applied.
Most Past Date or Empty
Finds the most past or empty date in a given set of dates. If one empty date is in the set, the result is empty.
Most Recent Date
Finds the most recent date in a given set of dates. The Date pattern of first selected input date will be applied.
Nested IF-THEN-ELSE
See the Transformation Rule - Nested IF-THEN-ELSE article.
Numeric Only
Remove all non-numeric characters
Quote Field
Puts the value of the field in quotes
Record Count Per Unique Key Value
Calculates the cardinal number of records with the same value(s) in the selected input fields.
Record Number Per Unique Key Value
Calculates the ordinal number amongst records with the same value(s) in the selected input fields
Try Format As Date
Tries to format an input value into a date value. Allowed format characters are y for year, M for month and d for day, and are case sensitive.
If the input field value cannot be formatted as a Date, then the original value in the input field will be returned.
Try Format As Decimal
Tries to format an input value into a decimal value.
If the input field value cannot be formatted as a Decimal, then the original value in the input field will be returned.
Try Format As Integer
Tries to format an input value into a decimal value.
If the input field value cannot be formatted as a Decimal, then the original value in the input field will be returned.
Use first non-null value
The first non-null value will be used. This rule will look in all records in the source file and use the first non-null value found. If no value is found for all records, a blank value will be returned.
Source field:
| Pay Date |
| 08/15/2023 |
| 12/13/2020 |
| 10/21/2020 |
For example, if we apply this rule to the Pay Date field above, the Date 08/15/2023 will be returned for all records.
Output field:
| Pay Date |
| 08/15/2023 |
| 08/15/2023 |
| 08/15/2023 |
| 08/15/2023 |
Use first non-null value if empty
If a null/empty value is found, the first non-null value will be utilized.
Source field:
| Pay Date |
| 08/15/2023 |
| 08/01/2023 |
| 08/15/2023 |
For example, if we apply this rule to the Pay Date field above, the Date 8/15/2023 will be returned for the field that is null/empty.
Output field:
| Pay Date |
| 08/15/2023 |
| 08/01/2023 |
| 08/15/2023 |
| 08/15/2023 |
Use last non-null value if empty
If a null/empty value is found, the last non-null value will be utilized.
Source field:
| Pay Date |
| 08/15/2023 |
| 09/15/2023 |
| 09/15/2023 |
For example, if we apply this rule to the Pay Date field above, the Date 09/15/2023 will be returned for the field that is null/empty.
Output field:
| Pay Date |
| 08/15/2023 |
| 09/15/2023 |
| 09/15/2023 |
| 09/15/2023 |
Week Number of Year
Returns the week number for the given date using the first day of the week specified.
For example, if we select this rule and specify Sunday as the first day of the week, the output for the date 08/15/2023 would be 33 (the 33rd week of the year).
