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.

To create a new lookup table for a transformation:

  1. From the Transfer Desk toolbar, click Lookup Table Designer. The Lookup Table Designer is displayed.
  2. In the Lookup Table Designer, select Edit > New Table or click Create a new Lookup Table.
  3. Enter a name for the lookup table and click OK.
  4. 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.
  5. From the Lookup Table Designer toolbar, click Add a new row to the currently selected Lookup Table. The Add New Row dialog is displayed. Enter the Range From and To fields to specify the range of data that the lookup table row applies to.
    Note: You cannot add rows where the range overlaps an existing row.
  6. From the Lookup Table Designer toolbar, click Add a new column to the currently selected Lookup Table. The Add New Column dialog is displayed. Enter a unique name for the column and click OK.
  7. From the Lookup Table Designer toolbar use the following buttons to define the new table:

Repeat steps 6 and 7 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.

Setting the Column Value Type for each row

Each row (or data range) can have different column value types. In the above example, Range From 1 To 9 might have a column value type of <copy> (Copy original value), and Range 10 To 999 might have a column value type of Constant, with value 9.

Click the query button to display the Column Value dialog. Select a value type from the drop-down list if required. Available values are:

  • Constant
  • Copy original value <Copy>
  • Map other source value <Map To>
  • Omit value <Omit>
  • Reverse sign <Reverse sign>.
Note: If you select either Constant or Map other source value as a value type you must also enter a parameter value.