To create rows for Missing Months

[praveen2410]

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.

[Vegar · Accepted solution]

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;