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

[MCFH93]

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

[marcus_sommer]

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. 

[MCFH93]

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.

[marcus_sommer]

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.