Combine 4 Different Tables into 1 Table

[MCFH93]

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

[marcus_sommer]

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.

[MCFH93]

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

[marcus_sommer]

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.

[MCFH93]

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?

[marcus_sommer]

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.

[MCFH93]

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

[marcus_sommer]

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

[MCFH93]

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.

[marcus_sommer]

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
...
];