topic Re: Incorrect Null count in straight table in QlikView

Incorrect Null count in straight table

clondono — Tue, 08 Mar 2016 16:28:55 GMT

Hello,

I have an issue with counting the number of rows containing Null value.

I'm trying to count the number of DISTINCT projects that have NULL values for the "Facility" field and a count of projects with "End dates" in the past. Not all projects have null values for this field and some projects might have more than one row with null value for the field.

If I create two different straight tables, one for end dates in the past and one for missing facilities, I get the correct count. However, I cannot get both fields in a single straight table because the missing facilities count is incorrect then. I am attaching a sample file with the error.

Any ideas on how to solve this issue?

Thanks!

Re: Incorrect Null count in straight table

cbaqir — Tue, 08 Mar 2016 22:11:10 GMT

sunindia‌

Re: Incorrect Null count in straight table

sunny_talwar — Tue, 08 Mar 2016 22:56:31 GMT

Not sure if this would still give you the correct number when you clear, but try this:

Count(DISTINCT{$<REQUEST_ID = {"=NullCount(TASK_FACILITY)>0"}>*<Flag_NullTaskSeq = {'*'}>}REQUEST_ID)

Re: Incorrect Null count in straight table

clondono — Tue, 08 Mar 2016 23:02:52 GMT

Thank you Sunny, you are awesome!

This solution works, also I found a solution from a post you responded a couple of minutes ago and I applied to this issue and it worked as well (Count number).

This is the expression I was using from the alternative solution:

Sum(Aggr(If(NullCount(TASK_FACILITY)>0, 1, 0),REQUEST_ID, [TASK_FACILITY]))

Re: Incorrect Null count in straight table

sunny_talwar — Tue, 08 Mar 2016 23:24:21 GMT

Great, I am glad I was able to help. But just so you know, prefer using Set Analysis over Aggr() wherever possible.