topic Re: count null and non null values in QlikView

count null and non null values

yelin_nyu — Mon, 18 Mar 2013 18:18:29 GMT

If I have a table like below

product week value

1, 1, null

1, 2, 123

2, 1, 333

2, 2 444

say I want to count values below 400 including nulls. I should get 3. how to include null values in counting?

Thanks

swuehl — Mon, 18 Mar 2013 18:32:07 GMT

Try something like

=NullCount(value) + Count(if(value<400,value))

or using set analysis

=NullCount(value) + Count({<value = {"<400"} >} value)

yelin_nyu — Mon, 18 Mar 2013 18:36:32 GMT

does nullcount() work in script? I am trying to say if( values <400 or len(values)=0, 'green') as color_flag.

And does not count the null values.

swuehl — Mon, 18 Mar 2013 20:35:28 GMT

You can use Nullcount in the script, but as with all aggregation functions, you probably need to use a group by clause.

If you just want to create a flag if value is <400 or NULL, you don't need a Nullcount or any other aggregation function.

Try

if(values < 400 or len(trim(values))=0,'green') as color-flag

the len(trim()) will take care of NULLs and blanks. There is also an isnull() function you can use (but I prefer len(trim()), it's more robust).

Regards,

Stefan