topic Re: class function padded with zeros in QlikView

class function padded with zeros

eddie666 — Tue, 08 Jan 2019 11:01:05 GMT

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

Re: class function padded with zeros

felipedl — Tue, 08 Jan 2019 11:43:37 GMT

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.

Re: class function padded with zeros

sunny_talwar — Tue, 08 Jan 2019 12:24:15 GMT

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;

Re: class function padded with zeros

eddie666 — Tue, 08 Jan 2019 12:48:07 GMT

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.