Table of Contents
REPLACE WITH LOOKUP
Category: Transform / Advanced
Description
This action replaces substrings in one or more columns using a lookup table.
Action settings
| Setting | Description |
|---|---|
| Lookup table | Select the dataset to be used for the lookup. |
| Substrings in | Select the column containing the lookup values in the lookup table. |
| Replace with | Select the column containing the replacement values in the lookup table. |
| Replace in columns | Select whether to perform the replacements in all columns, or specified columns. Options: Replace in all columns or Replace in selected columns (and select which columns to perform the replacements in). |
Remarks
Order of the records in the lookup table matters. Replacements occur in the order they are encountered in the lookup table. If a substring is replaced in an earlier match, an expected replacement may no longer apply. A source value may undergo multiple replacements based on its content at the time each lookup record is processed.
Examples
Example: Match the source value to any of the lookup values. (Illustrates the example statements given above in the Match mode settings table)
Table 1: Source value
| Source text |
|---|
| Motorola |
Table 2: Lookup table
| Lookup values |
|---|
| tor |
| Moto |
| rola |
| Motrola |
Action parameters:
Column is "Source text" (in Table 1)
Lookup table is "Table 2"
Lookup column is "Lookup values"
Case sensitive is unchecked.
If not matched, "Make empty"
Using the two tables and parameters above, the following tables show the results of the given settings.
Contains match mode:
| Source value |
|---|
| tor |
Starts with match mode:
| Source value |
|---|
| Moto |
Ends with match mode:
| Source value |
|---|
| rola |
Fuzzy match mode (max edit distance of 1):
| Source value |
|---|
| Motrola |