A VLookup transformation performs a Vertical Lookup. This means that for each value in the source field, it looks up a matching value in the lookup table within the lookup key field. If a match is found, the output is set to the value of the lookup value field in the matching row. This is similar to the VLookup functionality in spreadsheets.
Prerequisites
- You must have Manage Data Mappings permission.
- A syntactic mapping should have already been created.
Creating a VLookup Transformation
To create a VLookup transformation, complete the following steps.
Step 1: Open the Transformations Panel
- In the Syntactic Mappings page, choose the mapping you want to edit.
- Click the Edit Pencil in the Transformation field for the entry you would like to edit transformations for.
- The Transformations Panel will appear. Click the Add Transformation button.
- Click the dropdown menu to select the desired transformation type.
Select the VLookup transformation.
Step 2: Fill in the Transformation Configuration
- Note the Transformation Input and Transformation Output. It should reflect what you already chose in the syntactic mapping entry.
- Fill in the Lookup Table - this is a source data schema in which you want to match values from the source field.
- Fill in the Lookup Key Field - this is the field in the Lookup Table that should have values that match the values in the source field.
- Fill in the Lookup Value Field - this is the field in the Lookup Table from which the output will be filled in, if a matching row is found.
- Indicate what should happen if no match is found when looking up a specific source field value in the lookup key field. There are several options available: skip the row, cause the code run to fail, use the source value or set the output to a default value (return a number of string).
- Indicate what should happen if multiple matches are found when looking up a specific source field value in the lookup key field. There are several optional available: use the first match, use the last match, fail the code run, or set to a default value (return a number or string).
- When complete, select the Save button.
- Note that the source field values doesn’t need to be unique (the same value can appear multiple times in the source field and would be transformed to the same value in the output consistently).
Editing a Transformation
To edit a transformation, complete the following steps.
- In the Syntactic Mappings page, select the mapping that you want to edit transformations for.
- Find the mapping entry you want to edit, then click the pencil icon in the Transformation column. The Transformations Panel will appear.
- Edit the transformation as desired.
- When complete, click the Save button.