Defining a lookup table

Lookup tables define the mapping of codes and values in a journal file to Controller object codes so that the data can be used in Controller. For example, you often need to convert the account codes in the import file to Controller account codes.

About this task

A lookup table contains the following information:
  • Lookup table name
  • Lookup table description
  • The Controller object to convert a code to
  • Indicate whether to allow mapping of one value in the data file to more than one Controller object code (duplicates allowed). The mapping of one Controller object to many values in the data file is allowed by default.
You can define the mapping in a lookup table in the following ways:
  • By importing a .csv or .xlsx file that contains the mappings
  • By manually entering the mappings
You can use the wildcard character * in the string when you specify the values from the data file. The wildcard character indicates that one or more characters in the file can be of any value.
Note: Use caution when you specify the * in lookup tables where duplicates are allowed. If a value matches both a code with a * and a code with no * in the lookup table, the lookup table is invalid even if duplicates are allowed.

You can export and import the content of a lookup table to and from an .xlsx file.

Procedure

  1. To access the Lookup table, on the Import data screen, click Import settings.
    The Import settings window opens.
  2. Under Lookup tables tab, click Create lookup table.
  3. Type a Name and Description for the lookup table.
  4. Select the Controller Object that the lookup table applies to from the list.
  5. To allow mapping of one value in the data file to more than one Controller object code, select the Allow duplicates check box.
    If this check box is not selected, then each unique value in the data file can map to only one Cognos Controller object.
  6. Click Save.
    The lookup table is displayed in the Lookup tables list. The gray dot beside the name of the lookup tables means that it is empty.
  7. In the list, click the name of the lookup table.
  8. To import a file that contains the mappings, do the following steps:
    1. Click the import icon download icon..
      A warning message displays that the data in the lookup table is overwritten.
    2. Click OK.
    3. Drag a file to the import window or browse to the file to select it, and click Import.
      A message displays showing the number of rows imported.
    4. Click Close and then click Save.
  9. To manually enter the mappings, do the following steps:
    1. In the first row of the From column, type the value that is being imported.
      You can use the wildcard character * in this field.
      For example, you can use the wildcard character to specify a range of account codes, rather than specifying each account code separately.
    2. In the To column, select an account, company, or extended dimension that the imported data is to be put into.
    3. If the Change sign column is displayed, select Yes if you want to change the sign of the amount when it's imported.
      The Change sign column is displayed only for lookup tables for accounts.
    4. Click Add row and define its values.
    5. Continue adding rows until you have defined all of the fields for the data to be imported.
    6. Click Save.

Results

If the lookup table is valid, a green dot is displayed beside it.

If the lookup table is invalid, a red dot is displayed beside it. You must edit the mapping rows in the lookup table and make sure they are defined correctly.

What to do next

To change the name, description, Controller object, or whether duplicates are allowed, click the three dots icon beside the lookup table and select Edit.

To delete a lookup table, click the three dots icon beside it, and select Delete.