Combining Data Using a Lookup Pipe

Scenario

Combining a large stream with data from a small stream, where values in the small stream will only used once in the result. For each pair of matching records from the data sets, a single record is produced in the output.

Example

You have a stream containing all attendees of an upcoming football match and a small stream of people who are banned from attending matches.

In the below screenshot, 'Source Stream 1' about 2000 records and we want to enrich this data with data from 'Source Stream 2', which contains about 50 records.

The result stream type is set to 'Calculate'.

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

The pipe from 'Source Stream 2' is a lookup pipe. A filter should be added to define the joining key between the 2 streams

All stream 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 filter would be looking for records where Attribute1 is null, because it would not yet be calculated.

Watch out for the multiple records returned by your lookup pipe. You will either need to:

choose a record in each group (e.g. contacts.1.ContactName will choose the first record in the group),
use an aggregate function to find the best record in the the group (e.g. max(contacts.ContactName),
or tick 'multiplier' on the pipe, which will cause the resulting stream to display one row per record in the group, rather than one record for the group.

Further details on look up pipes can be found here: Difference between filter and order/index lookups