class function padded with zeros

[eddie666]

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

[felipedl]

You could use the alt function to alternate with the value you need, so once it has no value on the field you want, you'd get the 0, something like that:

Alt(class(kwh,1),0) as kWh_Interval_temp,

Something like that,
The alt() gets the first value that appears and is not null.

Hope it helps,

Felipe.

[sunny_talwar · Accepted solution]

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;

[eddie666]

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.