Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
Thx, it works. It seems so easy but for a newbie a little bit tricky.