Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
We have data in this format:
KPI | Market | Business | Month | Value |
A | 1/ | 11/ | January | 0.1 |
A | 1/ | 11/ | March | 0.2 |
A | 1/ | 11/ | June | 0.3 |
A | 1/ | 11/ | October | 0.4 |
A | 1/ | 11/ | November | 0.5 |
and I'd like to generate a table like this
KPI | Market | Business | Month | Value |
A | 1/ | 11/ | January | 0.1 |
A | 1/ | 11/ | February | |
A | 1/ | 11/ | March | 0.2 |
A | 1/ | 11/ | April | |
A | 1/ | 11/ | May | |
A | 1/ | 11/ | June | 0.3 |
A | 1/ | 11/ | July | |
A | 1/ | 11/ | August | |
A | 1/ | 11/ | September | |
A | 1/ | 11/ | October | 0.4 |
A | 1/ | 11/ | November | 0.5 |
A | 1/ | 11/ | December |
I'd like to avoid any loops if possible, since we have many KPI's, Markets and Businesses.
Here is one solution without any loops.
kpi_table:
LOAD *, KPI&Market&Business&Month as _fulldimkey
FROM source;
cartesian_product_zero:
Load distinct KPI, Market, Business FROM kpi_table;
Join (cartesian_product_zero)
Load * inline [
Month
January
February
March
...
];
Concatenate (kpi_table)
Load KPI, Market, Business, Month
From cartesian_product_zero
Where not exists (_fulldimkey, KPI&Market&Business&Month);
Drop table cartesian_product_zero;
Drop field _fulldimkey;
Here is one solution without any loops.
kpi_table:
LOAD *, KPI&Market&Business&Month as _fulldimkey
FROM source;
cartesian_product_zero:
Load distinct KPI, Market, Business FROM kpi_table;
Join (cartesian_product_zero)
Load * inline [
Month
January
February
March
...
];
Concatenate (kpi_table)
Load KPI, Market, Business, Month
From cartesian_product_zero
Where not exists (_fulldimkey, KPI&Market&Business&Month);
Drop table cartesian_product_zero;
Drop field _fulldimkey;