topic Re: Trying to get the null count. in QlikView

Trying to get the null count.

Wed, 25 Nov 2020 16:16:04 GMT

I have data as below

ID Date

2 1/26/2016

3
4 3/24/2016

5 4/25/2016

Trying to get 2 countS of ID's,one with no dates and one with dates.

1.Count(if(len(Date)) >0,Date)

2.Count(if(len(Date)) =0,Date)

1 st one is giving me Count = 5 and the other one is giving me 0 count.

Thanks.

Re: Trying to get the null count.

Mon, 19 Sep 2016 13:56:00 GMT

Correction..below is my expression.

1.Count(if(len(Date)) >0,ID)

2.Count(if(len(Date)) =0,ID)

Re: Trying to get the null count.

adamdavi3s — Mon, 19 Sep 2016 14:47:30 GMT

Try this if you're just looking for an overall count

=count(if(len(Date) >0,1))

=count(if(len(Date) <1,1))

Please remember to mark this as helpful or the correct answer if I have helped you or answered your question.

Re: Trying to get the null count.

Mon, 19 Sep 2016 14:54:21 GMT

Thank you .But the second expression is giving me 0 count.

Re: Trying to get the null count.

adamdavi3s — Mon, 19 Sep 2016 14:56:36 GMT

It sounds to me like it isn't blank....

I would drop them in a table chart with len(Date) as an expression and see!

You could try a quick: Count(if(len(trim(Date))) =0,Date)

Re: Trying to get the null count.

effinty2112 — Mon, 19 Sep 2016 14:58:12 GMT

Hi Pavana,

For ID with dates:

=count({$<Date = {'*'}>}ID)

For ID with no dates

=count({$-$<Date = {'*'}>}ID)

Cheers

Andrew

Re: Trying to get the null count.

Mon, 19 Sep 2016 15:15:55 GMT

You are right...even though date is blank in the file the len(Date) is 1 !!

Re: Trying to get the null count.

Mon, 19 Sep 2016 15:20:15 GMT

How can I write this in set analysis ?

Re: Trying to get the null count.

adamdavi3s — Tue, 20 Sep 2016 06:20:10 GMT

We'll need a bit more information to go on to advise I am afraid!

What is the formula you're trying to write?

Re: Trying to get the null count.

tamilarasu — Tue, 20 Sep 2016 06:52:41 GMT

Another solution using set analysis.

=Count({<Date={"=Len(Trim(Date))>0"}>} Date)

=Count({<Date={"=Len(Trim(Date))=0"}>} Date)