This article describes how to use the Replace/Remove transformation rules to either replace an existing value or remove a value from a field.
detamoov offers 5 Replace/Remove rule variations to allow for more flexibility when you need to replace or remove a value. These rules include:
Replace/Remove exact value using a list
Replace/Remove value using a list
Replace/Remove First Occurrence of a string
Replace/Remove Last Occurrence of a string
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. Define one replacement group per line using a pipe character to delimit the values. Example: '123|ABC' will replace 123 with ABC.
In the following example, we will be replacing the employee's Last Name First Name with their Employee ID. This feature can be helpful in the event that you have to include the Employee ID but it does not appear in the data sent to detamoov.
Say we have the following employees that we need to add their Employee ID:
Smith John
Brown Edward
Johnson Sally
Einstein Al
King Martin
Harris James
To apply a Replace/Remove transformation rule, we would select the Employee ID field and then select the First Name and Last Name fields from the Input Fields dropdown.
Our employees are listed as '[Last Name] [First Name]' in our example above, so we will need to rearrange the field sequence by dragging and dropping the Last Name field above the First Name field.
Next, we will concatenate the 2 fields with a space to get the correct employee name representation. Click the ADD Rule button and select Concatenate and enter in a space in the Separator field.
This rule is useful when you have a long list of values that you need to replace/remove. For our Employee ID example above, we would click the Add Rule button and select the 'Replace/Remove exact value using a list' rule. Then include a pipe delimited list of values for each employee and their ID in the Replacement Groups field.
Note: Harris James does not have an employee ID in the Replacement Groups field. This will ensure that no value is provided in the Employee ID for this employee.
As long as the employee's name matches the value in the Replacement Groups field, the corresponding ID value will now appear in the Employee ID field.
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. Define one replacement group per line using a pipe character to delimit the values. Example:'123|ABC' replace 123 with ABC.
This rule is similar to the Replace/Remove exact value using a list except that it will replace the value anywhere it is found in the field.
For example: say we have a City field in our data and we need to replace the word 'East' with 'West' (for whatever reason) in cities that currently have 'North' and 'South'. We would select the City field and set the Replace/Remove value using a list rule:
If our City value was 'North Hampton', then our output would be 'East Hampton' after applying this rule.
Replace/Remove First Occurrence of String
Replaces the first occurrence of a string in a string sequence with another string or removes it. Define string to be replaced in 'Replace' and string to replace it with in 'With'. Leave 'With' empty if you only want to remove a string without replacement.
This rule is useful when you have the same value more than once in a string and need to remove or replace only the first occurrence. For example, if our string contained 'Left Left Right':
Applying this rule would return 'Right Left Right' as it only replaces the first occurrence of 'Left' in the string.
Replace/Remove Last Occurrence of String
Replaces the last occurrence of a string in a string sequence with another string or removes it. Define string to be replaced in 'Replace' and string to replace it with in 'With'. Leave 'With' empty if you only want to remove a string without replacement.
This rule is the same as the Replace/Remove First Occurrence of String except it replaces or removes the last found occurrence of the specified value.
Replaces every occurrence of a string in a string sequence with another string or removes it. Define string to be replaced in 'Replace' and string to replace it with in 'With'. Leave 'With' empty if you only want to remove a string without replacement.
This rule is a more simple version of the Replace/Remove using a list rule and is good for when you only have a few values to replace or remove.
Using our employee example from above, we could use this rule to accomplish the same result by replacing the employee's name with their employee ID.
