Scenario

Combining a large

table with data from a small

table, where values in the small

table will be only used once in the

results. For each pair of matching records from the

record sets, a single record is produced in the output.

Example

You have a

table containing all attendees of an upcoming football match and a small

table of people who are banned from attending matches.

Solution

In the below screenshot, 'Source

Table 1'

has 2000 records and we want to enrich this data with data from 'Source

Table 2', which contains

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. A filter should be added to define the joining key between the 2

• tables.
• All

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

Image Added

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.

Learn More

For more detail on lookup pipes see Data and ERD Properties.

For help on Filtering Vs Order Index, which can impact performance, see Difference Between Filter and Order/Index Lookups.