VI Lookup Process Object

The Visual Integrator (VI) Lookup process object combines two input flows with a simple relational join to form a single output flow.

Icon for the VI Lookup Process Object

Two inputs are required: a primary input for the input flow and a secondary input for the lookup flow. Either a single column or multiple columns from each flow are used to match rows between the inputs. For each row in the input flow, the value of the match columns are used to find a row in the lookup flow.

  • If the lookup row is found, a set of columns are added to the input row as part of the output flow.
  • If no row is found, empty strings are inserted for those columns.
  • If multiple rows matching the join keys are found in the lookup input, only one of the rows is kept. A warning is issued, but the process continues using the first value encountered.

NOTE: Lookup inputs are fully loaded into memory. If you have large lookup input files, consider breaking them into smaller files. If you need to use these large lookup files, consider using a Join process object instead.

You can perform a ranged lookup, where rows are joined based on a range of values. This is useful when there are multiple values that map to a single value. Ranged lookups let you create small lookup files instead of large files. That is, instead of creating a row for each possible value and repeating the lookup value, you can create one row with a range of values for each lookup value. When editing, you only need to update one row rather than a series of rows.

The Lookup process object has Closedthree panes where you set attributes.

VI Lookup Object All Panes

You set attributes for the Lookup process object in the Closedobject attributes pane.

Example of Lookup Object's attributes pane

Attribute Description
Input

Defines the primary input for the Lookup object. The Input attribute and the Secondary_Input are required attributes.

  1. Place an input object on the task flow, and set its input attributes.

  2. Place a Lookup object on the task flow.

  3. With the Lookup object selected, click its Input attribute's browse button, and move the pointer to the task flow.

    Note that a Closeddotted-line connector is attached to the Lookup object.

    Connecting a primary input to a VI Lookup object

  4. Attach this connector to the primary input object.
Secondary_Input

Defines the secondary input for the Lookup object. The Secondary_Input and the Input are required attributes.

  1. Place a second input on the task flow, and set its input attributes.

  2. With the Lookup object selected, click the Secondary_Input attribute's browse button, and move the pointer to the task flow.

    Note that a Closeddotted-line connector is attached to the Lookup object.

    Making a secondary connection to VI Lookup object

  3. Attach this connector to the secondary input object.
Join_Type

Defines the join type to perform. The inner and outer terms are from relational database technology, but their meanings are different as used here. Select one of the following types:

  • inner (default)—All rows in the input flow appear in the output flow. If there are unreferenced rows in the lookup flow, they are ignored. If no lookup row is found for a given input row, blank values are returned for all columns in the lookup flow, even if they duplicate non-blank columns in the input flow.

  • outer—Similar to the inner join type, except that any non-matched rows in the lookup flow are added to the output, with empty strings inserted for all columns except those unique to the lookup flow.

  • multiply—Produces a Cartesian product between the input and lookup records. Every combination of input and lookup records are passed to the output flow.

    If there are no lookup records (an empty set), then there is no output. The Join With column in the column grid must be empty.

    Remember that the lookup input flow must fit into memory. The multiply join type is useful when you need to join a single record to all records in the main flow.

  • outer_update—Similar to the update join type, except that any non-matched rows in the lookup flow are added to the output, with empty strings inserted for columns only appearing in the input flow.

  • update—Lookup flow acts like a set of updates that modify or add to the input columns. Null values appearing in the lookup flow (as defined by the Update_Null_Value attribute) do not override values in the input flow.

    If no lookup row is found for a given input row, the columns in the input flow are unchanged, and new columns from the lookup flow are blank.

    If duplicate rows are present in the lookup flow with the same join values, they are merged on a column-by-column basis with the final non-null column value overriding earlier values.
Range_Type

Defines the range type in a ranged lookup. If performing a ranged lookup, select one of the following types:

  • no range lookup (default)—No range lookup is present.
  • numeric—Range check is performed using numeric comparisons. The range values and lookup range columns must be numeric.
  • strings—Range check is performed using string comparisons.
Case_Sensitive

Controls whether comparisons are case-sensitive.

  • true—Comparisons are case-sensitive.
  • false (default)—Comparisons are case-insensitive.
Update_Null_Value Defines the null value used for the update and outer_update join types. If a lookup column value matches this string, its value is considered null and does not affect the output. By default, this attribute is not specified, which indicates an empty string.

Each object has an area where you can enter comments.

It is a DI best practice to enter a note for every object in a VI script. You can set a VI preference to give a warning for each object without a note. For more information, see Visual Integrator Preferences.

ClosedLookup process object with comments

Example of comments for a VI Lookup object

The ClosedLookup column grid displays the primary input on the left, the secondary input on the right, and the input columns and the join columns that you specify in the middle.

Column grid example for VI Lookup Object

The Lookup column grid has three parts:

  • Primary Input Grid (left)—Lists the columns defined in the primary input object where you can select the columns to join. The Source Object column displays the object name where the columns entered the flow. Double-click the Source Object to change the task flow focus to that source object.
  • Join Grid (middle)—Displays the columns that you have designated for the join. See the following section about joining columns.
  • Secondary Input Grid (right)—Lists the columns defined in the secondary input object where you can select the columns to join with. The Source Object column displays the object name where the columns entered the flow. Double-click the Source Object to change the task flow focus to that source object.

To set the columns for the join:

  1. From the Primary Input grid on the left, select the column, and click the arrow between the left and middle grids.

    The column is added to the ClosedLeft Column column of the middle grid.

    Adding join column from primary input to VI Lookup object

  2. From the Secondary Input grid on the right, select the column you want to join, and click the arrow between the right and middle grids.

    The column is added to the ClosedJoin With column in the middle grid.

    Adding secondary join for VI Lookup object

  3. Repeat steps 1 and 2 for any additional joins.

  4. Edit the Join Order column if necessary.

    NOTE: Edit the Range End if working with a range lookup.