LOOKUP
Description
The LOOKUP operator allows you to lookup matching rows of a source column to a target table where the target and source tables cannot have a pre-defined join relationship in the Data Model.
It performs a left outer join between a target table and a source column which must have a N:1 relationship. Only equi-joins are supported. Multiple join conditions are combined together using AND logic.
Syntax
LOOKUP ( target_table, source_column, (join_condition) [, (join_condition),... ] )
target_table: Table to which the source should be joined. The target_table is on the N-side of the join.
source_column: Column to be joined to the target_table. The table of source_column is on the 1-side of the join. This cannot be a constant.
join_condition: At least one join condition must be provided. If multiple join conditions are provided, these are combined using AND logic. See below for the syntax.
The join_condition consists of columns from the target_table and the source_table where source_table is the table that source_column belongs to:
(target_table.column, source_table.column)
target_table.column: A column from the target_table.
source_table.column: A column from the source_table.
A single join_condition may be interpreted as target_table.column = source_table.column and multiple join conditions as target_table.col1 = source_table.col1 AND target_table.col2 = source_table.col2.
Filter behavior
As filters propagate via joins and LOOKUP only works on disconnected tables for which no direct or indirect join relations exist, filters that are not based on tables connected to the target_table are not applied.
Examples
[1] | ||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||
|
[2] | ||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||
|
[3] In this example we see how | ||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||
|