Spectre Build Lookup Object

The Spectre Build Lookup object allows you to add data from lookup tables before building the cBase. You can use a column or columns from your input data as key values to find a single line of data in a lookup table.

Spectre Build lookup object icon

The lookup object has multiple sections in the properties panel where you set attributes. This section is the same for each type of source data: text, cBase, ODBC, or Dive input.

Spectre Build Lookup Object Properties Panel

Each object has an area where you can enter comments for documentation purposes.

It is a DI best practice to enter a comment for every object in a Spectre build script.

Comments box

You select basic process options in the heading for the Lookup definitions and result columns panels.

Additional Lookup Properties

Attributes Description
Resolve Duplicates

Controls behavior for lookups.

The specifications are:

  • strict—lookup succeeds when there is exactly one match (this is the default)
  • any—any matching row in the lookup table can be used
  • first—the earliest match in the lookup table is used
  • last—the last match in the lookup table is used

The selection becomes relevant whenever a lookup table has more than one row with the same set of keys (or, in the case of range-keys, when the keys cannot resolve some row in the base table unambiguously to one row in the lookup table).

NOTE: By default (the strict option), when a build encounters duplicate lookup keys with identical values, that value is used. Otherwise, a warning is issued due to the ambiguity, and <unknown> is used instead. There is only one warning issued per lookup. Use the Resolve Duplicates set to any, first, or last to change this behavior. Best practice is to avoid duplicate or <unknown> keys.

Keys are Required Dimensions

Indicates that all columns brought in via the lookup are flagged as Infos off of the key dimension. Defaults to False.

You can review the data in use for the Main Flow and Lookup Flow panels.

Spectre Build Lookup Object - Main Flow and Lookup Flow

Section Attribute Description
Main Flow Column Name

Lists the column name as defined in the incoming data set.
  Column Type

Lists the data type as specified in the incoming data set.
  Source Lists the source object for the column.
Lookup Flow Column

Shows the column name as defined in the lookup table.
  Type

Indicates the data type as defined in the lookup table.
  Source Lists the source object for the column.
  Keep

Indicates if the column is to become part of the output flow. If none are selected, all are included. If any are checked, then only those are included.

You specify keys in the Lookup Definitions table. You can populate the Base Column by double-clicking a Column Name in the Main Flow; similarly, populate the Lookup Column by double-clicking a Column in the Lookup Flow.

Spectre Build Lookup Definitions

Attribute Description
Base Column

Indicates the key column in the primary data set. Regular lookup keys can be any type except for double.
Lookup Column

Indicates the key column in the lookup table.

Lookup End Column

Indicates a range-lookup. When an end column is included, the lookup column is assumed to be the start of the range.

The lookup is performed by connecting the key column to pairs of range columns in the lookup. A range-lookup takes three arguments:

  • Name of the key column in the main table (Base Column)
  • Name of the begin range column in the lookup table (Lookup Column)
  • Name of the end range column in the lookup table (Lookup End Column)

For a lookup row to match a main row, the main table's key value must be between the begin-range and end-range values, inclusively.

The begin-range and end-range values can be null, in which case the range is open on either side (or both sides).

Range lookup keys can be any type except for string.

NOTE: You can add additional rows to the Lookup Definition table so multiple keys can be declared, allowing for a multi-key join. Use the plus button to add rows.

You can examine the results in the Results Columns panel.

Spectre Build Lookup Object Results Columns

Section Attribute Description
Result Columns Column Name

Lists the column name in the output data set.
  Column Type

Lists the data type in the output data set.
  Source Lists the source object for the column.

TIP: If you want to rename columns before using them in the lookup, place a calc object between the input and lookup objects.

The lookup for this example uses a text input file. Other options are cBase, ODBC or Spectre dive input for the lookup table.