Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
baylor2016
Creator
Creator

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

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

1 Solution

Accepted Solutions
sunny_talwar

Try this

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

View solution in original post

11 Replies
sunny_talwar

Try this

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

baylor2016
Creator
Creator
Author

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

sunny_talwar

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)

baylor2016
Creator
Creator
Author

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

sunny_talwar

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?

baylor2016
Creator
Creator
Author

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.

baylor2016
Creator
Creator
Author

Hi Sunny,

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

sunny_talwar

Try this

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

baylor2016
Creator
Creator
Author

Works as expected. Thanks a million!!!