Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
The Factor data is retrieved from another table and join to this table with the common key of Scope.
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.
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.
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.