topic Re: How to get record count for null (blank or missing) ? in QlikView

How to get record count for null (blank or missing) ?

baylor2016 — Thu, 11 Jan 2018 20:32:25 GMT

Hi,

After reviewing many posts related to null(missing, blank) fields, I still cannot figure out how to get the correct counts for field whose value is null, missing or blank. can get the non-null counts by using =COUNT( {<Enddate={*}>} PAT_ID). For count of null field I used =COUNT( {<Enddate-={*}>} PAT_ID), it always return 0. I tried many different methods such as =COUNT( {<Enddate-={'-'}>} PAT_ID), still cannot get it right. Please advice. The sample project is attached.

Thanks

Longmatch

Re: How to get record count for null (blank or missing) ?

sunny_talwar — Thu, 11 Jan 2018 20:58:49 GMT

Try this

=COUNT( {<PAT_ID= {"=Len(Trim(Enddate)) = 0"}>} PAT_ID)

Re: How to get record count for null (blank or missing) ?

baylor2016 — Thu, 11 Jan 2018 21:02:04 GMT

Thanks. It works. By the way, why =COUNT( {<Enddate-={*}>} PAT_ID) is not working? I saw most people recommend this.

Re: How to get record count for null (blank or missing) ?

sunny_talwar — Fri, 12 Jan 2018 00:55:54 GMT

Set Analysis only work on a non-null cell value... wherever Enddate is null, it won't work.... You can create a flag in the script as an alternate solution

If(Len(Trim(Enddate)) = 0, 1, 0) as EnddateNullFlag

and then use this

=Count({<EnddateNullFlag = {'1'}>} PAT_ID)

Re: How to get record count for null (blank or missing) ?

baylor2016 — Wed, 17 Jan 2018 21:57:46 GMT

Hi Sunny,

I used the expression you recommended in Textbox and Straight table in the same sample project, but it behaves differently. The number returned is not what I expected.

=COUNT( {<PAT_ID= {"=Len(Trim(Enddate)) = 0"}>} PAT_ID)

Please refer to the attachment for details. Thanks

Longmatch

Re: How to get record count for null (blank or missing) ?

sunny_talwar — Wed, 17 Jan 2018 22:02:33 GMT

You are not doing a count, you are doing a distinct count.... since distinct count depends on dimension (startdate in this case)... table shows more than 3....what do you expect to see in straight table or text box object?

Re: How to get record count for null (blank or missing) ?

baylor2016 — Wed, 17 Jan 2018 22:07:27 GMT

I would like to get the count of patients whose address have no endDate. It sounds simple, but it is not easy to understand. Thank you for your always helps.

Re: How to get record count for null (blank or missing) ?

baylor2016 — Thu, 18 Jan 2018 16:24:36 GMT

Hi Sunny,

Do you have time to review my sample app? What is the problem? Thanks

Re: How to get record count for null (blank or missing) ?

sunny_talwar — Thu, 18 Jan 2018 16:28:37 GMT

Try this

='No enddate records: ' & COUNT(DISTINCT Aggr(If(Len(Trim(Enddate)) = 0, PAT_ID), PAT_ID, CITY, Enddate))

Re: How to get record count for null (blank or missing) ?

baylor2016 — Thu, 18 Jan 2018 16:52:22 GMT

Works as expected. Thanks a million!!!

Re: How to get record count for null (blank or missing) ?

baylor2016 — Fri, 19 Jan 2018 22:49:15 GMT

Thank you for your helps to solve my problem. Do you know why =COUNT( {<PAT_ID= {"=Len(Trim(Enddate)) = 0"}>} PAT_ID) does not work in straight table? Just curious. Thanks as always.

Re: How to get record count for null (blank or missing) ?

sunny_talwar — Sat, 20 Jan 2018 18:16:24 GMT

If you see the Aggr() function it needed to use three dimensions rather than just PAT_ID

Count(DISTINCT Aggr(If(Len(Trim(Enddate)) = 0, PAT_ID), PAT_ID, CITY, Enddate))

So, basically what you are testing is not just against PAT_ID like in the below expression

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

But against, three fields like this

=Count({<PAT_ID&CITY&Enddate = {"=Len(Trim(Enddate)) = 0"}>} PAT_ID)

But the above syntax is not right, so that is why you cannot do this is set analysis unless you create a new field which combines the three field into one in script

AutoNumber(PAT_ID&CITY&Enddate) as NewField...

Now you can use this

=Count({<NewField = {"=Len(Trim(Enddate)) = 0"}>} PAT_ID)

Does this make sense?