[Qlik Compose] How to load from multiple table to single table?

[iti-attunity-sup]

Hello support team.

[Info]
Source/Target : Oracle

[Question]
How to load from multiple table to single table?

-image
Source database → DWH

SRC_TABLE_A → TGT_TABLE
SRC_TABLE_B → TGT_TABLE
SRC_TABLE_C → TGT_TABLE

Source database tables are different table, but DWH(target database) table is same table.

Best Regards.

[sureshkumar · Accepted solution]

Hello @iti-attunity-sup 

If you have identical source tables that you want to combine into a single DWH table then we have a "Consolidation" feature for that.

For Different tables manually create a DWH table which contains all the columns from the source tables, once you have that, you can create several mappings to that DWH table, each mapping will be a from a different source table.

Regards,

Suresh

[TimGarrod]

This really depends on the requirements to load the data.  If this is a UNION / UNION ALL scenario you can simply - 

Define 3 mappings.  1 for each 'source' table to the DWH table.  These can be run in the same DW task (the task will load each of the source tables into staging and then process it all in parallel) or they could be run in different DW tasks if tehre is a dependency on an upstream process in the source. 

If the use case is a JOIN (FROM T1 JOIN T2 JOIN T3) - then you should handle that via a query based mapping or a view where you provide the join logic in the query based mapping / view. 

The Consolidation feature mentioned is built for scenarios where you have X tables with the same structure in a large number of sources. (eg. 10-20 sources with the same structures that you wish to Consolidate).  Consolidation allows you to do this in an automated way without creating duplicate mappings.    But if you use case is to do this for just a few DWH tables then the above approaches should be used. 

[Venus]

Did you try concatenate? If you have the same fields you can concatenate.

[iti-attunity-sup]

Thank you for your reply.

I have some more questions.

>If you have identical source tables that you want to combine into a single DWH table then we have a "Consolidation" feature for that.

Can "Consolidation" feature be used only if source tables are same name ?
If source tables have same business key values, which values is prefferd ?

>This really depends on the requirements to load the data. If this is a UNION / UNION ALL scenario you can simply -
>Define 3 mappings. 1 for each 'source' table to the DWH table.

What I would like to do is UNION / UNION ALL.
So, would you tell me detail about this way ?

Best Regards.

[TimGarrod]

Consolidation requires the tables and columns to be named the same, just in a different LANDING schema.  
If source tables have the same business key - it will depend on when you execute. If you want logic for that you should build that into the process either with custom SQL - or more commonly mapping to different fields and then performing that decision in the mart processing. 

You could also add a column to specify SRC_SYSTEM if you want all the records to be retained..

for UNION / UNION ALL and its a few tables - I would create a mapping for each table to the DWH and then process in the same task. 

[iti-attunity-sup]

Thank you for your reply.

I would like to ask you a little more.

>for UNION / UNION ALL and its a few tables - I would create a mapping for each table to the DWH and then process in the same task.

Which Does this mean setting source as Query using UNION/UNION ALL or creating some mappings to same DWH table ?

Because I don't know how to change target DWH table name.

Best Regards.

[iti-attunity-sup]

Hello

How is the situation ?

And I would like to ask you one more question.

If create some mappings to same DWH table, is there any way not to MERGE ?
Because when duplicate key exits, I would like Qlik Compose not to UPDATE.
Is there no way but to set source as Query ?

Best Regards.