Create multiple Row per Category based on Start and End Date

[MG82]

Hi,
i'am new in Qlik and I struggle with an Load Script that I need.
I've an annual Forecast with Categories as Rows and the Deparment as Columns. I want to split the values into a monthly View, so that I have for each Category and each department a single row per Month. The anual Value will be divided by the sum of months. In Excel it is a manual Part, but how to solve it in Qlik. The table looks like that.

I want wo have it like that, because I want to track the Actual (cumulative) Month Values with the forecast Values.
 

[rwunderlich · Accepted solution]

Here's one approach.  Use CrossTable to unwrap the departments and then a Join to add the additional columns. Using IterNo() as a loop counter to generate 12 months for each. 

Raw:
LOAD * INLINE [
Category, Dep 1, Dep 2, Dep 3
Shirts, 400, 950, 700
Cars, 500, 452, 600
Shoes, 800, 366, 200
]
;

Expanded:
CrossTable (Department, ValueTemp, 1)
LOAD Category, [Dep 1], [Dep 2], [Dep 3]
Resident Raw
;

Join (Expanded)
LOAD
Category,
Department,
num(ValueTemp / 12, '##0.0') as Value,
MakeDate(2021, IterNo()) as Month
Resident Expanded
While IterNo() <= 12
;

DROP Field ValueTemp;  

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[MG82]

Thx, it works. It seems so easy but for a newbie a little bit tricky.