Using lookup tables

Lookup tables are useful for replacing source data with the actual data that you want to appear in the Storage Zone. For example, a lookup table could be used to replace a zip code with a full address or, conversely, to replace a full address with a zip code.

Linking lookup tables

To link a lookup table column to a Storage Zone table column:

1. Click the link to the desired task in the Storage Zone panel.

   The Manage Tasks window opens.

2. In the Mappings column, click the mapping for the Storage Zone table containing the result column (with the data that you want to replace).

   The Edit Mapping - Name window opens.

3. Hover the mouse cursor over the relevant Storage Zone column and then click the Lookup button that appears to the right of the column name.

   The Select Lookup Table window opens.

   1. From the Database drop-down list, select the database containing the lookup table.

      Information note

      The database must reside in your Landing Zone.

   2. From the Schema drop-down list, select the schema containing your source lookup tables.
   3. Select either Table or View according to the lookup table type.

   4. From the Table drop-down list, select the lookup table.

      The right side of the Select Lookup Table window displays the lookup table columns and their data types. To view the data in the lookup table, click the Show Lookup Data button.

   5. After you have selected the lookup table, click OK.

   The Lookup Transformations - Table Name.Column Name window opens.

   The window is divided into the following panes:

   Upper pane: The upper part of the right pane (Condition) displays the condition expression, which stipulates the condition(s) for performing the lookup.

   Lower pane: The lower part of the right pane (Result Column) displays the column result expression, which stipulates what data to replace in the target column.

4. To change the lookup table, click the Change Lookup Table button above the lookup table columns and then perform steps a. to d. above.
5. To view the lookup table or landing table data, click the Show Lookup Data or Show Landing Data buttons respectively.

6. To specify the condition(s) for performing the lookup, click the Create Expression button (which changes to Edit Expression after an expression has been created) above the Condition expression.

   The Condition Expression - Column Name window opens.

7. Create an expression using the landing and lookup table columns on the left.

   For an example, see Using lookup tables.

   For information on creating expressions, see Creating transformations.

8. To specify what data to replace or add if the lookup conditions are met, click the Create Expression button (which changes to Edit Expression after an expression has been created) above the Result Column expression.

   The Result Expression - Column Name window opens.

9. Create an expression using the landing and lookup table columns on the left.

   For an example, see Using lookup tables.

   For information on creating expressions, see Creating transformations.

10. To preview the results, click the Preview Results button.
11. Click OK to save your settings and close the Lookup Transformations - Table Name.Column Name window.

Lookup example

The following example shows how a lookup table is used to concatenate a Dutch translation of the category name (located in the lookup table) to the original category name located in the landing table.

The lookup could be defined using the following expressions:

1. Condition expression:

   ${Lookup.CategoryID}=${Landing.CategoryID}

   Meaning: Perform the lookup only if the Category ID in the landing table and the lookup table are the same.

2. Result column expression:

   ${Lookup.CategoryName} + ' is ' + ${Landing.CategoryName}

   Meaning: Add the data in the CategoryName column in the lookup table to the data in the CategoryName column in the landing table (separated by the word "is").

Assuming the result column name is "Split Name", clicking the Preview Results button would display the following table: