topic Re: How to include nulls and exclude a specific value? in QlikView

How to include nulls and exclude a specific value?

Anonymous — Tue, 24 Jan 2017 15:02:28 GMT

Hi,

I have a table that will count the IDs, but I need to exclude some values, the problem is that the field may have nulls or blanks.

I am using something like this:

count({$<[Code] -= {'Test'}>}[Case Count])

When I use something like that, it will also remove from the list the ones that are null. How can I use a similar expression that not exclude nulls?

Please let me know.

Re: How to include nulls and exclude a specific value?

sunny_talwar — Tue, 24 Jan 2017 15:08:25 GMT

May be do this in the script:

LOAD Code,

[Case Count],

If(Code = 'Test', 0, 1) as Flag

FROM ....;

and then this:

Count({<Flag = {1}>}[Case Count])

Re: How to include nulls and exclude a specific value?

Anonymous — Tue, 24 Jan 2017 15:25:16 GMT

Is there another way to do it, directly on the Expression?

Re: How to include nulls and exclude a specific value?

sunny_talwar — Tue, 24 Jan 2017 15:26:56 GMT

You can try this:

Count({$<[Code] = e({<Code = {'Test'}>} Code)>}[Case Count])

Re: How to include nulls and exclude a specific value?

Anonymous — Tue, 24 Jan 2017 15:30:20 GMT

That works but excluded the nulls. I need the nulls in the view.

Re: How to include nulls and exclude a specific value?

sunny_talwar — Tue, 24 Jan 2017 15:33:12 GMT

Do you have a unique identifier in your table? Lets say you have UniqueKey as unique identifer....

Count({$<UniqueKey = {"=Code <> 'Test' or Len(Trim(Code)) = 0"}>}[Case Count])

Re: How to include nulls and exclude a specific value?

Anonymous — Tue, 24 Jan 2017 15:43:41 GMT

count({$<UniqueKey = {"=[Code]<>'test' or Len(Tim([Closure Code])) = 0"}>}[Case Count])

I am getting errors in expression message.

Re: How to include nulls and exclude a specific value?

sunny_talwar — Tue, 24 Jan 2017 15:45:13 GMT

Do you have field name UniqueKey in your application? I guess not, it was just a place holder to replace with a field which uniquely identify each row from the table which include Code and Case Count.

Does that make sense?

Re: How to include nulls and exclude a specific value?

Anonymous — Tue, 24 Jan 2017 15:49:35 GMT

Yes, makes sense. But it's still not working, not showing anything.

Count({$<CaseId= {"=Code <> 'Test' or Len(Trim(Code)) = 0"}>}[Case Count])

Re: How to include nulls and exclude a specific value?

sunny_talwar — Tue, 24 Jan 2017 15:51:16 GMT

How about just this?

Count({$<CaseId= {"=Code <> 'Test' "}>}[Case Count])

Re: How to include nulls and exclude a specific value?

swuehl — Tue, 24 Jan 2017 15:51:50 GMT

Maybe

Count({$<[CaseId] = e({<Code = {'Test'}>} )>} [Case Count])

Excluding values in Set Analysis

Re: How to include nulls and exclude a specific value?

sunny_talwar — Tue, 24 Jan 2017 15:58:50 GMT

Or this:

Count(If(Code <> 'Test', [CaseCount]))

Re: How to include nulls and exclude a specific value?

Anonymous — Tue, 24 Jan 2017 16:03:32 GMT

Thank you for your answer, this is working!!!!

Re: How to include nulls and exclude a specific value?

sunny_talwar — Tue, 24 Jan 2017 16:05:31 GMT

And this did not work?

Count({$<CaseId= {"=Code <> 'Test' "}>}[Case Count])

swuehl‌ is there a difference between the above and your expression?

Re: How to include nulls and exclude a specific value?

sunny_talwar — Tue, 24 Jan 2017 16:09:51 GMT

I am not sure what might have gone wrong, but seems like most of the things I proposed are working on this sample . But I might not know your data well enough to comment more

Re: How to include nulls and exclude a specific value?

Anonymous — Tue, 24 Jan 2017 16:38:51 GMT

It worked but excluded the nulls, the one from Stefan didn't excluded the nulls. Thank you both for your help.

Re: How to include nulls and exclude a specific value?

sunny_talwar — Tue, 24 Jan 2017 16:41:11 GMT

Thanks for getting back .

If you look at the sample attached below, I don't see that behavior.... Anyways in the end you got what you wanted.

Best,

Sunny