Creating lookup tables

Lookup tables allow you to define lists of 'transformed output values' dependent on ranges of source data.

Note: Lookup functionality uses alpha collation only, that is, text strings in alphabetical order. For example, lookup tables can be used for ranges of account codes and analysis codes, as they are alphanumeric. However, it is not recommended to use lookup tables for ranges of numeric data, such as dates, journal numbers or financial amounts.

Complete these steps to create a new lookup table for a transformation:

  1. Open Transfer Desk and select Configuration Lookup Table Designer.
    The Lookup Table Designer opens.
  2. Click New.
  3. Specify Name and Business Unit
    Note: Use the Business Unit field to select the required business unit that contains the specific data for the new lookup table, or leave it as the default, Global Data, if you want the table to apply to all business units.
  4. Create rows and columns for the table:
    1. Click Add row. Specify values in the Range From and To fields to describe the range of data to which the lookup table row applies.
      Note: You cannot add rows where the range overlaps an existing row.
    2. Click Add column. Enter a unique name for the column and click OK.
    3. Repeat steps a and b to add as many columns and rows as you need in order to create a transformation lookup table to your requirements.

    For example, you might have a column called Output_Value, with two rows defined. The first row might have Range From 001 To 009, and the second row Range From 010 To 999.