Nested Lookups

[andreabeschi]

Hello,

I'm trying to create the following result in Compose:

I have 3 tables:
A
Col1, Col2

B
Col2, Col3

C
Col1, Col4, ...

I need to join Table A with Table B (Key is Col2),
and then Join the resulting table with the Table C (Key is Col1): The goal is having the Col3 in the Table 3.

C
Col1, Col4, Col2, ...


I can use the lookup function in the DWH Mappings, but only for joining Table A with Table B,
since in the lookup menu I can select columns only from the landing zone.
 
How can I perform this lookup sequence in Compose?

Thank you

[TimGarrod · Accepted solution]

Currently the only way to perform a "multi-table lookup" in Compose is to create a view in the DW that tables Table2 and Table3 and use that for a lookup. 

Another method to solve this requirement would be to normalize the model slightly - have Table1, Table2 and Table3 defined in the model with appropriate relationships, and then in the Data Mart dim / fact design - pull in the required tables/columns

[mikelflood]

---

@TimGarrod wrote:

Currently the only way to perform a "multi-table lookup" in Compose is to create a view in the DW that tables Table2 and Table3 and use that for a lookup.  png to ico

Another method to solve this requirement would be to normalize the model slightly - have Table1, Table2 and Table3 defined in the model with appropriate relationships, and then in the Data Mart dim / fact design - pull in the required tables/columns

---

Thanks guys. It was helpful