Compose Task - Update 3 target tables

[Pierre_G]

I have a situation where I need to update 3 DW tables from 1 _CT table.   I am using a simple province / city example to describe what I want to achieve. It might look silly

province_ct --> maps to dw.province
city_ct --> maps to dw.city
city_log_ct --> maps to dw.city_log  (regular mapping)

city_log_ct --> maps to dw.city  (city and city_log  have same granularity / nb of records. city_log contains extra columns to save in dw.city)
city_log_ct --> maps to dw.province (province has different granularity. need to update province.max_log_date from max(city_log_ct) for province)

How can this be achieve in compose DW?  (not Data Mart)

[shashi_holla]

Hi @Pierre_G 

I think this can be achieved through Model setup by creating attributes and relationship between the tables. For detailed info please check this user guide link:

https://help.qlik.com/en-US/compose/August2021/Content/ComposeDWDL/Main/DW/Model/set_up_derived_attr...

Thank you,

[Pierre_G]

@shashi_holla I have created the necessary relationships in the Compose Model. Unfortunately, when creating an expression using the guide you provided, The GUI does not present the related table columns to chose from. 

[shashi_holla]

Hi @pierre 

If this doesn't actually help then we can either create a view or use query based approach with the required tables and see if it works as expected.

Thank you,

[deepaksahirwar]

Hello @Pierre_G ,

In such scenarios, on the model phase you will need to create the relationship, attributes and entities manually instead of Auto Discover.

i.e 1. Creating the model manually.

2. In Datawarehouse phase do the thorough mappings manually and populate your data.

You will achieve the expected output.

Ref. https://help.qlik.com/en-US/compose/August2021/Content/ComposeDWDL/Main/DW/Model/set_up_derived_attr...

Thanks and Regards,

Deepak Ahirwar 

[Pierre_G]

I have created a new project and created the model manually including relationships. In the DW phase,I created a mapping, mapped the keys and I clicked on the FX (created derived attribute) to pick data from related tables (based on model manual relationships)

At this point, I do not see any columns coming for the related tables.  I only see columns from the Source Table.

Also, maybe this is not important but, the FX screen gives me this title (Data Warehouse Columns) 

And not this (Staging Columns) as presented in the help https://help.qlik.com/en-US/compose/August2021/Content/ComposeDWDL/Main/DW/Model/set_up_derived_attr...

[jtompkins]

You will need to have a query or a view with the necessary joins as your source for the map if you want to create expressions that require attributes from multiple tables. 

[Pierre_G]

I have created a view with the necessary joins as a source. Works for full load but not for CDC, it is looking for the view__ct

[robertcur]

Pierre_G,

Updating from __ct table is merely the concept of CDC.  You actually use the main table for your full load and then the CDC setup will automatically use the __ct table.  Up to a recent version (I'm not 100% which one), once the __ct table has been read, it will be truncated, but now with a later version, you can tell the thing not to drop __ct table as you might wat to read into more than one DW table.

Then in the last __ct run, you can use POST processing (I think this is the way, not sure) to copy your data to the __ar table (if used) and truncate __ct table.

You can then read via multiple mappings into your 3 DW tables.

Let me know if this helps.