User Tools

Site Tools


transformations:replacewithlookup

REPLACE WITH LOOKUP

Category: Transform / Advanced


Description

This action replaces substrings in one or more columns using a lookup table.


Action settings

SettingDescription
Lookup tableSelect the dataset to be used for the lookup.
Substrings inSelect the column containing the lookup values in the lookup table.
Replace withSelect the column containing the replacement values in the lookup table.
Replace in columnsSelect 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


See also

transformations/replacewithlookup.txt · Last modified: 2021/07/19 02:19 by craigt