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.

The matching of the source table's values to the lookup table's "Substrings in" field's values is case-sensitive. (e.g., "The" will not match with "the".) Be sure to include all case combinations that may occur in the lookup table.


Examples

Example: Convert the abbreviated values in the "Address Line 2" field with their full-text versions.

Table 1: Source table

Address Line 2
Apt 2
St. A
st 422
6th flr
apt 12
flr 8

Table 2: Lookup table

SourceOutput
St.Suite
StSuite
st.Suite
stSuite
Apt.Apartment
AptApartment
apt.Apartment
aptApartment
Flr.Floor
FlrFloor
flr.Floor
flrFloor


Action parameters:

Lookup table is "Table 2"
Substrings in is "Source"
Replace with is "Output"
Replace in all columns


Result table

Address Line 2
Apartment 2
Suite A
Suite 422
6th Floor
Apartment 12
Floor 8


Note how the values in the "Source" column of the Lookup table took into account both capitalized and uncapitalized versions of the lookup values, as well as versions having trailing periods.


See also

transformations/replacewithlookup.txt · Last modified: 2023/06/26 23:29 by craigt

Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki