topic Re: Combine 4 Different Tables into 1 Table in QlikView

Combine 4 Different Tables into 1 Table

MCFH93 — Wed, 05 Jun 2024 07:40:32 GMT

Hi,

Can I ask on how do I combine 4 different tables into 1 Table as shown below.

I can do the expression formula to put the 4 total into the new table but I do not know how to write the dimension to combine all the tables months since all tables have different months columns.

Thank you for all your advise and help.

Table 1

Month 1	Total 1
Jan-2023	0
Feb-2023	0
Mar-2023	0
Apr-2023	0
May-2023	27
Jun-2023	0
Jul-2023	0
Aug-2023	0
Sep-2023	0
Oct-2023	0
Nov-2023	0
Dec-2023	0

Table 2

Month 2	Aug-2023	Sep-2023	Oct-2023	Nov-2023	Dec-2023	Jan-2024	Feb-2024	Mar-2024	Apr-2024	May-2024
Total 2	0	0	0	6,000	0	0	0	0	22,789	7,300

Table 3

Month 3	Total 3
Apr-2023	2,556
May-2023	2,193
Jun-2023	2,401
Jul-2023	2,176
Aug-2023	2,557
Sep-2023	2,835
Oct-2023	2,886
Nov-2023	3,077
Dec-2023	2,898
Jan-2024	2,721
Feb-2024	2,969
Mar-2024	2,528
Apr-2024	3,038
May-2024	3,091

Table 4

Month 4	Total 4
Jan-2023	0
Feb-2023	0
Mar-2023	0
Apr-2023	0
May-2023	1
Jun-2023	0
Jul-2023	0
Aug-2023	0
Sep-2023	0
Oct-2023	0
Nov-2023	0
Dec-2023	0

Expected Table

	Jan-2023	Feb-2023	Mar-2023	Apr-2023	May-2023	Jun-2023	Jul-2023	Aug-2023	Sep-2023	Oct-2023	Nov-2023	Dec-2023	Jan-2024	Feb-2024	Mar-2024	Apr-2024	May-2024
Month 1	0	0	0	0	0	27	0	0	0	0	0	0	0	0	0	0	0
Month 2	0	0	0	0	0	0	0	0	0	0	6,000	0	0	0	0	22,789	7,300
Month 3	0	0	0	2,556	2,193	2,401	2,176	2,557	2,835	2,886	3,077	2,898	2,721	2,969	2,528	3,038	3,091
Month 4	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0
Total	0	0	0	2,556	2,194	2,428	2,176	2,557	2,835	2,886	9,077	2,898	2,721	2,969	2,528	25,827	10,391

Re: Combine 4 Different Tables into 1 Table

marcus_sommer — Wed, 05 Jun 2024 14:38:55 GMT

Just unify the field-names, maybe with something like:

load Month1 as Month, Total1 as Value, 1 as Source from 1;
load Month3 as Month, Total3 as Value, 3 as Source from 3;
load Month4 as Month, Total4 as Value, 4 as Source from 4;
crosstable(Month, Value, 1) load 2 as Source, * from 2;

and then using Source as vertical dimension + Month as horizontal dimension in a pivot-chart with sum(Value) as expression.

Re: Combine 4 Different Tables into 1 Table

MCFH93 — Thu, 06 Jun 2024 04:05:38 GMT

Hi, would it be possible to create a new chart table from the 4 existing table with different months range instead of using the load script?

Table 1

	Jan-2023	Feb-2023	Mar-2023	Apr-2023	May-2023	Jun-2023	Jul-2023	Aug-2023	Sep-2023	Oct-2023	Nov-2023	Dec-2023	Total (Till Date)
Refrigerant (R134A, kg)	0	0	0	27	0	0	0	0	0	0	0	0	27

Table 2

	Jun-2023	Jul-2023	Aug-2023	Sep-2023	Oct-2023	Nov-2023	Dec-2023	Total (Till Date)
Refrigerant (R410A, kg)	0	0	0	0	0	0	0	9

Table 3

	Apr-2023	May-2023	Jun-2023	Jul-2023	Aug-2023	Sep-2023	Oct-2023	Nov-2023	Dec-2023	Total (Till Date)
Generators (Diesel, litres)	0	0	0	0	0	0	6,000	0	0	170,922

Table 4

	Mar-2023	Apr-2023	May-2023	Jun-2023	Jul-2023	Aug-2023	Sep-2023	Oct-2023	Nov-2023	Dec-2023	Total (Till Date)
Motor Vehicle (Diesel, litres)	2,556	2,193	2,401	2,176	2,557	2,835	2,886	3,077	2,898	2,721	135,747

Expected Output Table

	Jan-23	Feb-23	Mar-23	Apr-23	May-23	Jun-23	Jul-23	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23	Total (Till Date)
Refrigerant (R134A, kg)	0	0	0	27	0	0	0	0	0	0	0	0	27
Refrigerant (R410A, kg)	0	0	0	0	0	0	0	0	0	0	0	0	9
Generators (Diesel, litres)	0	0	0	0	0	0	0	0	0	6,000	0	0	170,922
Motor Vehicle (Diesel, litres)	0	0	2,556	2,193	2,401	2,176	2,557	2,835	2,886	3,077	2,898	2,721	135,747

Re: Combine 4 Different Tables into 1 Table

marcus_sommer — Thu, 06 Jun 2024 05:15:07 GMT

No - it won't be possible. I assume that your question relates to the needed efforts and knowledge for the task and if it could be a bit shortened. In this regard I could ensure that the above suggested approach to load all sources in a single fact-table by unifying fields and data as much as possible is the most simple and easiest way.

Re: Combine 4 Different Tables into 1 Table

MCFH93 — Thu, 06 Jun 2024 05:54:08 GMT

Hi,

As Table 1 - Table 4 was created from formula in expression and dimension and not directly retrieved from the source file, therefore will the method of unifying fields and data still work in this case?

Re: Combine 4 Different Tables into 1 Table

marcus_sommer — Thu, 06 Jun 2024 06:12:45 GMT

It's not clear how the sources look like and with which measurements and where this table-deriving was performed and why?

Beside of this would the above suggested data-structure (related to the provided example fields) with the 3 fields of Month + Value + Source be ideally for nearly all kind of views.

Re: Combine 4 Different Tables into 1 Table

MCFH93 — Wed, 12 Jun 2024 02:57:35 GMT

Hi,

I managed to display the table as per your advise. Thanks so much for your help.

Can I also ask whether I can add another vertical dimension to the pivot-chart without messing the display?

Current Display

Expected New Display

Re: Combine 4 Different Tables into 1 Table

marcus_sommer — Wed, 12 Jun 2024 05:01:46 GMT

Yes - as far as each scope has a single factor-value. If there are more than ones they would create n rows.

Re: Combine 4 Different Tables into 1 Table

MCFH93 — Wed, 12 Jun 2024 07:05:38 GMT

Hi,

I tried to add another vertical dimension to the pivot-chart but it did not give me the expected output display as shown above.

I tried to add the new vertical dimension before the source, between the source and month and after month.

Can advise on this? Thanks.

Re: Combine 4 Different Tables into 1 Table

marcus_sommer — Wed, 12 Jun 2024 07:41:25 GMT

Are they other dimensions properly associated to the scope? Directly side by side from one table or linked in a way like:

t: load * inline [
Scope, Factor
A, 0.55
B, 1,1
...
];

Re: Combine 4 Different Tables into 1 Table

MCFH93 — Wed, 12 Jun 2024 08:04:08 GMT

The Factor data is retrieved from another table and join to this table with the common key of Scope.

Re: Combine 4 Different Tables into 1 Table

marcus_sommer — Wed, 12 Jun 2024 08:24:54 GMT

In this case make sure that the key-values have the equally structure in regard to being numbers or strings and have an identically format / cases.

Further take a look on the relationship between the tables. If they isn't 1:1 you may get (unwanted) duplicated records. In this case you may skip the join and just associated the tables or replacing the join with a mapping or extending the key to further fields like the periods. It depends on the existing data and the wanted views which approach would be the most suitable ones.

Re: Combine 4 Different Tables into 1 Table

MCFH93 — Tue, 18 Jun 2024 04:27:09 GMT

Can I ask how do I add a new row called Month5 which will display same value for each of the month. There is no table for Month5.

Re: Combine 4 Different Tables into 1 Table

marcus_sommer — Tue, 18 Jun 2024 05:51:14 GMT

You may add new rows with a concatenate-statement like:

concatenate(MyTable)

load 'MyValue' as MyField autogenerate 1;

autogenerate must not be 1 else n ones and the n values might be then divided with pick() or similar or you used an inline-table - but if it aren't just a few rows you should consider to create these records within an Excel or txt-file and load then from there.