Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hello
I am trying to generate Probability Distributions from many years worth of data.
I can use the class function to split the data into buckets:
eg:
table1:
LOAD * INLINE [
ID2,kwh
1,2
1,3.2
1,1
1,99
1,3.2
1,34];
[PDF Table template]:
LOAD
[ID2],
// MonthName($(vMonth_and_Year)) AS PDF_Date_temp,
class(kwh,1) AS kWh_Interval_temp,
count(kwh) AS NumberofkWh
RESIDENT table1 GROUP BY [ID2], class(kwh,1);
Qlikview will be efficient and will generate the class interval (or bucket) only where I have data. I don't want that.
I want it to give me all the class intervals, and if there is no data then put a zero in, so it gets padded with zeros.
is it possible to do this?
Thanks
You will have to generate the missing kwh to be able to do this.... try something like this
table1: LOAD * INLINE [ ID2, kwh 1, 2 1, 3.2 1, 1 1, 99 1, 3.2 1, 34 ]; Concatenate(table1) LOAD ID2, Min + IterNo() - 1 as kwh, 1 as Flag While Min + IterNo() - 1 <= Max; LOAD ID2, Min(kwh) as Min, Max(kwh) as Max Resident table1 Group By ID2; [PDF Table template]: LOAD [ID2], Class(kwh,1) as kWh_Interval_temp, Count(If(Flag <> 1, kwh)) as NumberofkWh Resident table1 Group By [ID2], Class(kwh,1); DROP Table table1;
You will have to generate the missing kwh to be able to do this.... try something like this
table1: LOAD * INLINE [ ID2, kwh 1, 2 1, 3.2 1, 1 1, 99 1, 3.2 1, 34 ]; Concatenate(table1) LOAD ID2, Min + IterNo() - 1 as kwh, 1 as Flag While Min + IterNo() - 1 <= Max; LOAD ID2, Min(kwh) as Min, Max(kwh) as Max Resident table1 Group By ID2; [PDF Table template]: LOAD [ID2], Class(kwh,1) as kWh_Interval_temp, Count(If(Flag <> 1, kwh)) as NumberofkWh Resident table1 Group By [ID2], Class(kwh,1); DROP Table table1;
hey, thank you.
I couldn't get ALT to work, but generating extra values for kwh has worked.
Actually I think your script with the Flag setting has helped with another problem I have had. Thank you for the advice.
fingers crossed.