Qlik Compose -Post ETL

[Megha_More]

Hello Team,

I tried to join two tables in post etl, but i'm unable to do it.

Used mention query:

SELECT
A.[OrderID]
, A.[Freight]
, B.[EmployeeID]
, B.[FirstName]

into Postloading_jointable2
FROM [Megha_dwh_qdi4].[test].[TDWH_Orders_HUB] A
left join [Megha_dwh_qdi4].[test].[TDWH_Employees_HUB] B
on A.[EmployeeID]= B.[EmployeeID]

Is there any way except lookup ?

How can we achieve this ?

Regards, Megha

[TimGarrod]

Can you explain your use case?   In Compose if you wish to denormalize tables after the central DWH, its typically handled in the data mart which automates that processing based on the relationships defined.

Also - can you explain what you mean by "I'm not able to".  What isn't working?

Note if you have a relationship defined between ORDERS and EMPLOYEES - then ORDERS will have an Employees column which contains the surrogate key (ID column) from the EMPLOYEES HUB.   

So a join would typically be - 

SELECT
A.[OrderID]
, A.[Freight]
, B.[EmployeeID]
, B.[FirstName]

into Postloading_jointable2
FROM [Megha_dwh_qdi4].[test].[TDWH_Orders_HUB] A
INNER join [Megha_dwh_qdi4].[test].[TDWH_Employees_HUB] B
on A.[Employee]= B.[ID]

And you can use an inner join if the relationship exists because all ORDERS will be tied to an EMPLOYEE record - whether an actual employee or the row where ID = 0 which will join to all ORDERS with a NULL value for EmployeeID

[Megha_More]

Hello,

Thanks, I used same join query , got the output. But is this worked only in full load ? I tried but it shows CDC not supported.

[TimGarrod]

Not sure what you mean ? If you are doing it as a Post-LOAD ETL step in Compose then you can assign the step to a FULL LOAD AND a CDC task.
As I said previously -would be good to understand the use case - what are you trying to do ? Can data marts accommodate this denormalization for you (even if you don't need a full data mart, just a denormalized type 1 or type 2 objects you can use the data mart capability to automate this for you).

[Megha_More]

Hi,

My use case is i need to join two tables, so i write query in post etl, but it only does full load, CDC is not working,

[TimGarrod · Accepted solution]

So after you load the DWH - you want to join 2 tables and create that as another table (not managed y Compose just another structure you are creating yourself). 

If you are writing the code, then Compose is not doing anything related to full OR CDC for you.  At that point we are simply orchestrating the code that you write.  If your code is a SELECT INTO X FROM Y JOIN Z -- I'm not sure how you expect Compose to handle 'CDC' for you, when its code you wrote ?

You can use the data mart capability in Compose to generate a type 1 or type 2 dimension.  This will join / denormalize based on your data model and your selection criteria and will initially perform a full load and subsequent iterations will be incremental.   Note - taht in this case Compose is generating the code for you. 

If you are writing your own code then Compose is just orchestrating your logic and you would need to handle incremental processing yourself... Since you wrote the code 🙂