Use the Key Lookup operator to lookup (retrieve) data from a set of data (filter,table, view, cube, or dimension …)
For example, use the Key Lookup operator
The Simplified Sql statement is :
select tableA.*
from
tableA,
tableB
where
tableA.value1 = tableB.value1 and
tableA.value2 = tableB.value2
Here, tableA is the lookup table. It's the table where we want to retrieve the data.
You have the following options to have access to the properties of create a Key Lookup operator:
You must go to the input connections page and do your choice with moving element from the left pane to the right pane. Here for instance, we chose for tableB three values :
To define the end of the lookup condition, go to the lookup page.
To provide the lookup details, select values for the following:
tableA.value1 = tableB.value1 and
tableA.value2 = tableB.value2
For instance
In this page, we have defined this sql simplified instruction :
select customer.*
from
customer,
tableB
where
customer.sales_organisation = tableB.sales_organisation_id
customer.division = tableB.division_id
customer.customer = tableB.customer_id
Use this page only if you selected a Type 2 SCD as the lookup result on the Lookup page. When the lookup result is a Type 2 SCD, you must specify which version of a particular record is to be used as a lookup. The options you can choose are as follows:
Use the No-match Rows page to indicate the action to be taken when there are no rows that satisfy the lookup condition specified on the Lookup page (Orphan Management).
Select one of the following options:
This option returns a row that contains default values when the lookup condition is not satisfied by the lookup result. Use the table below this option to specify the default values for each lookup column.
You can set the default value as an attribute of the IN group such as the below picture