About: This article introduces the Lookup node, a Compile node within Construct.
Location: Node panel
Table of Contents
Feature Overview
The Lookup node provides a fast and efficient way to retrieve values from a lookup table. The Lookup node is best used to match records on a reference sheet. For example, matching text labels to codes. One data source will appear on the left and labeled Input Source. This will be your "master data table." The second will appear on the right and labeled Lookup Source. This will be your "reference table."
Configuring a Lookup Node
The order the two data sources get connected to the Lookup node is important. The first table connected to this node will be the Input Source, and the second connection will be the Lookup Source.
Note: You can switch the order of sources within the node, by selecting Edit > Swap Sources from the toolbar at the top of the node.
Once the sources are connected, the two datasets need to be matched by a particular field that exists in both tables. Find the shared column in the Input Source, and add it to the Lookup By parameter using the arrow button. Then do the same from the Lookup Source. Finally, use the arrows on the lower section to add or remove fields from the Lookup Source into the Columns to Add section to be included in the output.
The final output will be the entire Input Source as it existed prior to this node, as well as any new columns that were added from the Lookup Source joined on the Lookup By field and tacked to the right end of the dataset.
Example
Consider a dataset containing historic matches of World Cup Soccer games. The master dataset has data points of: year of the tournament, home and away team names, goals, specific conditions on how the game was won, etc. However, the name of the country that hosted the tournament is missing. Luckily there is a separate data source that has a record of every year the tournament took place and the name of the country that hosted that year.
To add the country column to the "master" dataset of World Cup games, the two datasets will be matched by year since both sources contain this field. To do this, click Year in the Lookup Source section and use the arrow button to add it to the Lookup By parameter. Then do the same with Year from the Lookup Source. Finally, select Country and use the arrow in the lower section of the window to add it to the Columns to Add section. When this job is run, the result will be the master data table records and a column of country name as it corresponds to the matching year the tournament was played.
Note: Multiple columns can be selected to add at once by holding down the control or shift key and selecting columns.
Additional Notes
The lookup column is limited to 10,000 unique values in the Lookup Source. This makes sense, because this node is meant to match codes to translations, rather than to merge on advanced conditions. In addition, those unique values must be in alphanumeric order and contain no null values.
The Lookup node is not a replacement for the Merge node, despite similarities in functionality. Unlike the Merge node, the Lookup node only matches on one criterion and only accepts two input sources.
Video Tutorial
Comments
0 comments
Article is closed for comments.