Differences

This shows you the differences between two versions of the page.

--- 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.|
 |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).|
+|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.
+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.
@@ Line 22: / Line 24: @@
 =====Examples=====
-**Example:** Match the source value to any of the lookup values. (Illustrates the example statements given above in the //Match mode settings// table)\\
+**Example:** Convert the abbreviated values in the "Address Line 2" field with their full-text versions.\\
 \\
-**Table 1:** Source value
+**Table 1:** Source table
-^Source text^
+^Address Line 2^
-|Motorola|
+|Apt 2|
+|St. A|
+|st 422|
+|6th flr|
+|apt 12|
+|flr 8|
 **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:**
->Column is "Source text" (in Table 1)
 >Lookup table is "Table 2"
->Lookup column is "Lookup values"
+>Substrings in is "Source"
->Case sensitive is unchecked.
+>Replace with is "Output"
->If not matched, "Make empty"
+>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|
->**Starts with** match mode:
+|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 value^
-|rola|
-\\
->**Fuzzy** match mode (max edit distance of 1):
-^Source value^
-|Motrola|
 \\