transformations:replacewithlookup
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revision | |||
transformations:replacewithlookup [2021/07/19 02:19] – [Examples] craigt | transformations:replacewithlookup [2023/06/26 23:29] (current) – correct examples craigt | ||
---|---|---|---|
Line 13: | Line 13: | ||
|Substrings in|Select the column containing the lookup values in the lookup table.| | |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 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. | + | |Replace in columns|Select whether to perform the replacements in all columns or specified columns. |
\\ | \\ | ||
=====Remarks===== | =====Remarks===== | ||
Order of the records in the lookup table matters. | Order of the records in the lookup table matters. | ||
+ | |||
+ | The matching of the source table' | ||
Line 22: | Line 24: | ||
=====Examples===== | =====Examples===== | ||
- | **Example: | + | **Example: |
\\ | \\ | ||
- | **Table 1:** Source | + | **Table 1:** Source |
- | ^Source text^ | + | ^Address Line 2^ |
- | |Motorola| | + | |Apt 2| |
+ | |St. A| | ||
+ | |st 422| | ||
+ | |6th flr| | ||
+ | |apt 12| | ||
+ | |flr 8| | ||
**Table 2:** Lookup table | **Table 2:** Lookup table | ||
- | ^Lookup values^ | + | ^Source^Output^ |
- | |tor| | + | |St.|Suite| |
- | |Moto| | + | |St|Suite| |
- | |rola| | + | |st.|Suite| |
- | |Motrola| | + | |st|Suite| |
+ | |Apt.|Apartment| | ||
+ | |Apt|Apartment| | ||
+ | |apt.|Apartment| | ||
+ | |apt|Apartment| | ||
+ | |Flr.|Floor| | ||
+ | |Flr|Floor| | ||
+ | |flr.|Floor| | ||
+ | |flr|Floor| | ||
\\ | \\ | ||
**Action parameters: | **Action parameters: | ||
- | >Column is " | ||
>Lookup table is "Table 2" | >Lookup table is "Table 2" | ||
- | >Lookup column | + | >Substrings in is "Source" |
- | >Case sensitive | + | >Replace with is "Output" |
- | >If not matched, | + | >Replace in all columns |
\\ | \\ | ||
- | Using the two tables and parameters above, the following tables show the results of the given settings.\\ | + | **Result table** |
- | \\ | + | |
- | >**Contains** match mode: | + | |
- | ^Source value^ | + | ^Address Line 2^ |
- | |tor| | + | |Apartment 2| |
- | \\ | + | |Suite A| |
- | > | + | |Suite 422| |
- | + | |6th Floor| | |
- | ^Source value^ | + | |Apartment 12| |
- | |Moto| | + | |Floor 8| |
- | \\ | + | \\ |
- | >**Ends with** match mode: | + | 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.\\ |
- | + | ||
- | ^Source | + | |
- | |rola| | + | |
- | \\ | + | |
- | > | + | |
- | + | ||
- | ^Source value^ | + | |
- | |Motrola| | + | |
\\ | \\ |
transformations/replacewithlookup.txt · Last modified: 2023/06/26 23:29 by craigt