| Insert excerpt | ||||||||
|---|---|---|---|---|---|---|---|---|
|
Scenario
Combining a large
table with data from a small
table, where the values of the very small
table will be repeated throughout the result. For each pair of matching records from the data sets, a single record is produced in the output.
Example
Find the description for each code in a
table of thousands from a
table containing mapping data. There are only ~100 possible codes.
Solution
- To do this, use a calculate
- table with an order/index lookup pipe.
- In the below screenshot, 'Source
- Table 1' about 2000 records and we want to enrich this data with data from 'Source
- Table 2', which contains about 50 records.
- The
- result table type is set to 'Calculate'.
- The pipe from 'Source
- Table 1' is a pull pipe with no grouping.
- The pipe from 'Source
- Table 2' is a lookup pipe. An Order/Index entry should be added to define the joining key between the 2
- tables.
- This will index all the records from the source
- table 2 by the index attribute, so they can be searched quickly. The data will be queried once and the result put into memory.
- All
- attributes use the attribute name, prefixed by the pipe name. For example, in1.Attribute1.
You need to make sure that all attributes that you refer to with _out prefixes in the joining key have a lower order number than those that use the lookup pipe prefix. For example, in the above screenshot, it is essential that Attribute1 has a lower order number than Attribute3. If the order of the attributes were switched around, Attribute3 would not return a value, because the order/index would be looking for records where Attribute1 is null, because it would not yet be calculated.
| Info |
|---|
Watch out for the multiple records returned by your lookup pipe. You will |
need to do one of the following: |
|
|
|
|
|