Lookup Table

../../../../../_images/lookup.svg

None

Ports:

Inputs:

lookup:

table

Lookup table

lookupee:

table

Control table

Outputs:

out:

table

Result table

Configuration:

template_columns
A list storing columns in the lookup table used for matching. At each index there is a corresponding entry in lookupee_columns.
lookupee_columns
A list storing columns in the control table used for matching. At each index there is a corresponding entry in template_columns.
event_column
Stores the index of the column pair which is to be matched as events, or -1 if no such column pair has been selected.
perfect_match
If checked, each row in the control table has to match exactly one row in the lookup table.
nomasks
When unchecked rows that can not be matched will be masked. When checked such rows are instead assigned 0, 0.0, False, “”, etc. depending on the type of the value column.

The lookup node takes rows from a lookup table (upper port) in the order defined by a control table (lower port).

In the gui you can define pairs of columns, one from the control table and one from the lookup table. During the execution of the node, it will step through the rows of the selected control columns and try to find a match among the rows in the corresponding columns in the lookup table. If there is a match, that entire row in the lookup table is copied to the output.

Since there is one row in the output for each row in the control table, the output from this node should always be possible to hjoin with the control table. This is often a useful first step for using the output from this node.

Rows not found lookup table

The behavior when some rows in the control table can not be found in the lookup table depends on the state of the two checkboxes below the table with column pairs: Require perfect match and Use zero-like value instead of masks. By default both boxes are unchecked which means that any rows that can not be found in the lookup table are masked in the output.

If Use zero-like values instead of masks is checked, unmatched rows are instead assigned appropriate zero-like values depending on the data type of the columns.

Examples of zero-like values for different column types:

float:0.0
integer:0
bool:False
text:“”

This setting is mostly for backwards compatibility and is therefore not recommended for new flows.

If Require perfect match is checked an exception will be raised if row can not be matched. So with this setting all rows in the control table must be matched if the node is to execute successfully. This can be useful to avoid trickier errors in later nodes.

Using an event column

In the configuration GUI you can also choose to treat one of the defined column pairs as event columns. The event columns will typically consist of dates or times. But can actually be of any sortable data type. Only one event column pair can be chosen at a time.

When an event column pair has been defined, the matching is performed in a slightly different way. Each row in this control table will now be matched with the last preceding event in the lookup table which also matches in all other column pairs. This means that an entry in the event column of the control table doesn’t have to have an exact match in the lookup table for there to be a match. It is enough that there is any preceding event.

class node_lookup_table.LookupTableNode[source]