topic Age buckets and nulls in QlikView

Age buckets and nulls

Tue, 01 Dec 2015 22:04:45 GMT

Hi,

I wish to create age buckets in my list box. I have a lot of NULL values in my data. So, I tried using the class() function but the NULL (which was at the top of the list box originially) is not shown now. I want to have NULL at the top of my list box too. How can I do it?

Also, my age bucket shows as '15<=x<35'. I feel it will not look good on a chart. How can I change it?

Desperately in need of help.

Thanks in advance.

Re: Age buckets and nulls

swuehl — Tue, 01 Dec 2015 22:23:51 GMT

If NULL is appearing the list box, then you are talking about a text value 'NULL', right?

Then maybe something like

=If( Age = 'NULL', Age, Class(Age, 10) )

as calculated dimension, list box expression or in a LOAD Statement like

LOAD

Age,

If( Age = 'NULL', Age, Class(Age, 10) ) as AgeBucket,

....

And how do you want your classes to be formatted?

Re: Age buckets and nulls

Tue, 01 Dec 2015 23:04:32 GMT

I would prefer to have the classes as '15-25' format.

Also, Will having NULLS in the data source affect the visualizations in any way? I had nulls and blank spaces in my original datasource. How should i go about it after I load the data into QlikView?

Re: Age buckets and nulls

MarcoWedel — Wed, 02 Dec 2015 00:30:23 GMT

Hi,

one solution for real null values could be:

=Aggr(If(IsNull(age),Dual('null',-1),Dual(Replace(Class(age,20,'x',5),'<= x <','-'),Floor(age,20,5))),ID)

hope this helps

regards

Marco