{{ transformations:ReplaceWithLookupAction.png}}
====== REPLACE WITH LOOKUP ======
Category: Transform / Advanced\\

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

\\
=====Use cases=====
  * Used to replace common misspellings with the correct values.
  * Used to replace abbreviations with the full values.

\\ 
=====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 values 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.

This action differs from the [[transformations:lookup|Lookup]] action by looking up and replacing //substrings// as opposed to //full cell// values.


\\ 
=====Examples=====

====Example #1====
>Convert the abbreviated values in the "Address Line 2" field with their full-text versions.\\

===Before (source table)===
Table 1: Address lines
^Address Line 2^
|Apt 2|
|St. A|
|6th flr|

Table 2: Lookup values
^Source^Output^
|St.|Suite|
|Apt|Apartment|
|flr|Floor|

===After (result table)===
^Address Line 2^
|**Apartment 2**|
|**Suite A**|
|**6th Floor**|

===Action parameters===
>Lookup table: Table 2
>Substrings in: Source
>Replace with: Output
>Replace in all columns

\\ 
=====See also=====
  * [[transformations:lookup|Lookup]]
  * [[syntax:functions:contains|Function syntax: Contains(textA, textB)]]
  * [[syntax:functions:startswith|Function syntax:  StartsWith(textA, textB)]]
  * [[syntax:functions:endswith|Function syntax:  EndsWith(textA, textB)]]
  * [[syntax:functions:distance|Function syntax:  Distance(textA, textB)]]