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

1. To do this, use a calculate table with an order/index lookup pipe.
2. 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.
3. The result  table type is set to 'Calculate'.
4. The pipe from 'Source Table 1' is a pull pipe with no grouping.
5. 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.
6. 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.
7. All attributes use the attribute name, prefixed by the pipe name. For example, in1.Attribute1.