Scenario

Combining a large stream table with data from a small streamtable, where the values of the very small stream 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 stream table of thousands from a stream table containing mapping data. There are only ~100 possible codes.

Solution

1. To do this, use a calculate stream table with an order/index lookup pipe.
2. In the below screenshot, 'Source Stream Table 1' about 2000 records and we want to enrich this data with data from 'Source Stream Table 2', which contains about 50 records.
3. The result stream result  table type is set to 'Calculate'.
4. The pipe from 'Source Stream Table 1' is a pull pipe with no grouping.
5. The pipe from 'Source Stream Table 2' is a lookup pipe. An Order/Index entry should be added to define the joining key between the 2 streamstables.
6. This will index all the records from the source stream 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 stream attributes use the attribute name, prefixed by the pipe name. For example, in1.Attribute1.

Image Removed

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.

Image Added