Factory Lookup Rules

A lookup rule retrieves information from a lookup table by matching certain values from each record with values on a particular row of the lookup table.

A common use for a lookup rule is to convert codes into text descriptions. For example, a lookup rule can convert a revenue code to a revenue description. The lookup rule looks in the revenue lookup table to find the code for each record and returns the corresponding description into a new rule in the data set.

First, you need to declare the lookup table in the lookup-tables section of the factory configuration file, as shown in the following example:

lookup-tables {

lookup-table "Revenue" {

key "Revenue Code"

column "Revenue Description" type="string"

}

}

The key contains data that is common to the data set and lookup table.

The column contains data that you want to bring into the data set from the lookup table.

Then, you call the lookup table and set the rule within the data-set block.

data-set "Charges" {

cbase-input "/data/charges.cbase"

lookup "Revenue" {

date rule="Posting Date"

key "Revenue Code"

lookup-rule "Revenue Description"

}

}

This lookup rule specifies the following values:

  • lookup—Retrieve information from the Revenue lookup table that is declared in the lookup-tables code block.
  • date rule—The relationships between the codes and descriptions are effective as of the Posting Date, which comes from the source data.
  • key—The Revenue Code column contains the codes to look up.
  • lookup-rule—For each matched code, returns the Revenue Description column value from the same row.

You can use the same lookup file more than once in the same data set by using the long form of the lookup tag.

The syntax for the lookup tag example above uses a shorthand syntax. That is, it assumes the lookup name and table name are the same. The full syntax is:

lookup "<lookup_name>" table="<lookup_table>"

In this example

lookup "Airport-codes"

you can leave out the table= tag because it is the same as the lookup name.

To have multiple lookups in a data set use the same table, you can use the long form and use different names for the lookup name. For example:

lookup "Airport-codes by Origin" table="Airport-codes"

lookup "Aiport-codes by Destination" table="Airport-codes"

See also: