Join two tables in compose

[Aami]

Hi everyone,

Total newbie here, so any help is appreciated. I have two tables A & B
A
C1 C2 C3 --> C1 is Key
B
C2 C4 C5 --> C2 is key
My requirement is to join A and B on C2 and bring C3 into the output flow with table A into data warehouse. Kindly help me with this scenario.

Thanks,

[TimGarrod · Accepted solution]

Hi Aami,
AS with most data integration products, there are different methods to accomodate this in Compose.

Option 1 - Use a LOOKUP in the ETL Mapping. The Lookup will perform a JOIN and allow you to map column from the lookup table (in this case B) and bring it into the target entity in the DWH Model.
Note that when running CDC workloads - changes to the lookup table do not drive the etl. (Only change to the table defined as the mapping source are seen).
Option 2 - Leave teh data in a normalized state in the model. A and B are managed as separate entities and use the data mart layer denormalize the data into the dimension / fact table.

There is a whitepaper posted on community.qlik.com that discusses the modeling approach and benefits to CDC / incremental ETL and can be found here - https://community.qlik.com/t5/Qlik-Compose-for-Data-Warehouses-Documents/Modeling-Real-time-Data-War...

Hope that helps.

[joeyrohr]

---

@TimGarrod wrote:
Hi Aami, happy wheels
AS with most data integration products, there are different methods to accomodate this in Compose.

Option 1 - Use a LOOKUP in the ETL Mapping. The Lookup will perform a JOIN and allow you to map column from the lookup table (in this case B) and bring it into the target entity in the DWH Model.
Note that when running CDC workloads - changes to the lookup table do not drive the etl. (Only change to the table defined as the mapping source are seen).
Option 2 - Leave teh data in a normalized state in the model. A and B are managed as separate entities and use the data mart layer denormalize the data into the dimension / fact table.

There is a whitepaper posted on community.qlik.com that discusses the modeling approach and benefits to CDC / incremental ETL and can be found here - https://community.qlik.com/t5/Qlik-Compose-for-Data-Warehouses-Documents/Modeling-Real-time-Data-War...

Hope that helps.

---

Thank you I will give it a look