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

1. Table 1' about 2000 records and we want to enrich this data with data from 'Source

1. Table 2', which contains about 50 records.
2. The

1. result  table type is set to 'Calculate'.
2. The pipe from 'Source

1. Table 1' is a pull pipe with no grouping.
2. The pipe from 'Source

1. Table 2' is a lookup pipe. An Order/Index entry should be added to define the joining key between the 2

1. tables.
2. This will index all the records from the source

1. table 2 by the index attribute, so they can be searched quickly. The data will be queried once and the result put into memory.
2. All

1. attributes use the attribute name, prefixed by the pipe name. For example, in1.Attribute1.

Image Added