lookup

This operator left joins two tables (left, right) on left.lookupFields = right.lookupFields and adds fetchFields column to the left table. This operator is useful if the input table has numerous columns, which can make it difficult to use a simple join.

Operator Usage in Easy Mode

1. Click + on the parent node.
2. Enter the Lookup operator in the search field and select the operator from the Results to open the configuration form.
3. In the Table drop-down, select the step from which to source the data.
4. In the Lookup Table dropdown, select the lookup table for the join.
5. Match fields from the input table and the lookup table by selecting from the dropdown menus. Select a column from the input table and lookup table and indicate whether to make the match case sensitive. To add additional matching pairs, click Add; to remove a pair, click the trashcan icon.
6. Match fields from the lookup table to the output table by selecting from the dropdown menus. If the field you select for the lookup table has the same name as a field in the output table, that field is automatically selected but can be changed. Alternatively, click Add All Fields to include all fields in the match.
   To add additional matching pairs, click Add; to remove a pair, click the trashcan icon.
7. Click Run to view the result.
8. Click Save to add the operator to the playbook.
9. Click Cancel to discard the operator form.

Usage Details

lookup(table, lookupFields, lookupTable, fetchFields)

Input

table: Input table.
lookupFields: Column names that exist in both tables for matching.
lookupTable: Name of the lookup table.
fetchFields: Columns in lookup table to add to the input table.

Output
The output table is the same as input table with an additional columns named in fetchField. If fetchField column already exists in input table the original one is dropped.

Example

Input
table A with (f1, f2, f3) columns,

B = select f1 as u1, SIZE(f2) + SIZE(f3) as val, f2 as u2 from A
C = lookup(A,"[(f1, u1, match-case), (f2, u2)]", B, "[(val, val2)]")

-- C should be a table A with additional "val" column

-- The operator should produce the same output as
-- select f1, f2, f3, val as val2 from A join B on upper(A.f1) = upper(B.u1) and A.f2 = B.u2