Once created, a Transformation Lookup Table must be configured to store original text and the replacement text.
- In Tree, select the context that is required for the lookup table.
- Navigate to the lookup table or use the Search option to find and select it. The Transformation Lookup Table editor is displayed.
- If necessary, under the Description flipper, edit the name of the lookup table. All other default parameters are read-only and cannot be changed via the editor.
- In the Lookup Table section, choose a method to define the From-To replacement pairs:
- Manually - This method allows you to add replacement pairs one at a time. Click the Add row link to add a new row to the table or use the Tab key to move to a new row.
- Import from Clipboard - This method allows you to replace all existing values in the lookup table with values from the clipboard. First copy the From-To replacement pair values from, for example, Excel or an HTML table, to the clipboard. Then, click the Import From Clipboard button to paste values from the clipboard into the table.
Important: Generally, transformation lookup tables can be applied to attribute values or to text strings using one of two application methods: Match and Replace Entire Attribute Value / Text String; Match and Replace Words in the Attribute Value / Text String. Consider the application method when setting the following options. For more about the application methods, refer to the Using Transformation Lookup Tables topic in the Resource Materials online help here.
- For Replace with default value when no matches are found (Value Substitution only) checkbox:
- Checked indicates the text entered in the text box will be used as a default replacement if the value being transformed does not match any of the entries in the From column. For example, if you are importing values into a List of Values (LOV) and want to make sure all values match at least a default entry in the LOV. Only use when the Transformation Lookup Table will replace entire value / string.
- Unchecked indicates that no transformation happens when a value being transformed does not match any of the entries in the From column.
- For Replace with a source value when no matches are found and default value is empty (Value Substitution Only) checkbox:
- Checked indicates the value is replaced with the source value when no matches are found and the default value is empty.
- Unchecked indicates that empty values will be shown as a transformation result if the default value transformation is empty and the transformed value is also empty.
This value is unchecked by default.
- For Ignore Case checkbox:
- Checked indicates values or words in the data to be transformed are matched with entries in the From column regardless of the case. In this example, 'Colour', 'COLOUR' and 'colour' are all replaced with 'color'.
- Unchecked indicates that no transformation happens when a value being transformed does not match the case of the From column.
Important: It is not possible to preserve the case settings of the matched nor the replaced word or value. This means if a mapping of 'azure' to 'blue' has been configured and case is ignored, when applied to a string 'Azure and violet', the result would be 'blue and violet', not 'Blue and violet'.
- Click Apply to accept the current state of the lookup table.