topic Re: Set Analysis - Counting NULL Values in QlikView

Set Analysis - Counting NULL Values

Wed, 20 Jan 2010 22:42:16 GMT

I'm trying to count NULL values using Set Analysis. I basically use this code:

Count({$<[EagleFeedback.Win/Loss]={}>} [EagleFeedback.Win/Loss])

It never returns any values even though I know there are some.

How do I use Set Analysis to count NULL values?

Set Analysis - Counting NULL Values

Wed, 20 Jan 2010 23:34:18 GMT

I was able to get it to work using some of my data with:

Count({$-<[EagleFeedback.Win/Loss]={}>} [EagleFeedback.Win/Loss])

EDIT: I edited my comment. On my first reply, I was using blank value, not null values. This new expression counts everything and then subtracts anything with a valid entry in that field. I think that should give you the count of truly null values.

Set Analysis - Counting NULL Values

Thu, 21 Jan 2010 01:00:37 GMT

I saw something like that too. But I thought "...={}" specifically selected NULL values. That being the case, I would expect the negative sign would EXCLUDE NULL values.

I infer from your comment that "...={}" selects everything. I thought I read in the documentation that "...={}" selects NULL value. Did I read it wrong?

BTW, thanks for you response. It's truly appreciated.

Set Analysis - Counting NULL Values

mike_garcia — Thu, 21 Jan 2010 01:18:13 GMT

Maybe something like:

Count({$<[EagleFeedback.Win/Loss]= {$(=null())}>} [EagleFeedback.Win/Loss])

Cheers.

Set Analysis - Counting NULL Values

Thu, 21 Jan 2010 01:45:43 GMT

Sorry, ...{$(=null())}>} doesn't work.

Also, ({$-<[EagleFeedback.Win/Loss]={}>} from NMiller above doesn't count the NULL values -- it counts ALL (NULL and not NULL); that is, it excludes NOTHING which leaves EVERYTHING.

Thanks for trying Mike.

Set Analysis - Counting NULL Values

mike_garcia — Thu, 21 Jan 2010 17:38:29 GMT

Turns out there's a function specifically for this situation... Use:

=NullCount([EagleFeedback.Win/Loss])

Mike.

Set Analysis - Counting NULL Values

Thu, 21 Jan 2010 17:47:59 GMT

Thanks Mike. I need to figure our how to do this with set analysis. I have a complex (for me) set analysis statement that wasn't working and whittled down the problem to the above statement.

Set Analysis - Counting NULL Values

mike_garcia — Thu, 21 Jan 2010 18:04:42 GMT

Just did a little test and this expression worked fine:

Count({$<isnull([EagleFeedback.Win/Loss])= {'-1'}>} [EagleFeedback.Win/Loss])

Mike.

Set Analysis - Counting NULL Values

Fri, 22 Jan 2010 19:10:33 GMT

I humbly acknowledge the problem discussed in this thread is entirely my fault. But possibly it can be of assistance to others:

WHEN LOOKING FOR NULL VALUES, MAKE SURE NULLS ARE "NULL" AND NOT "-".

Thanks everyone and again, sorry.

Set Analysis - Counting NULL Values

Thu, 09 Sep 2010 14:12:28 GMT

Hi,

I had same problem and my solution was:

COUNT(
{$-<
EagleFeedback.Win/Loss = -{}
>}
EagleFeedback.Win/Loss
)

Regards,

Antanas

Set Analysis - Counting NULL Values

Anonymous — Thu, 24 Mar 2011 14:02:00 GMT

How about:

SUM(-ISNULL([EagleFeedback.Win/Loss]))

I was having the same problem and this is what I ended up with. Set Analysis is very finicky about null values.

Hope this helps anyone else.

Re: Set Analysis - Counting NULL Values

erjohnso — Thu, 08 May 2014 14:55:12 GMT

If your EagleFeedback.Win/Loss carries only 'Win', 'Loss' or NULL values, try this:

Count({$-1<[EagleFeedback.Win/Loss]={'Win','Loss'}>} [EagleFeedback.Win/Loss])

Re: Set Analysis - Counting NULL Values

antoniotiman — Thu, 08 May 2014 15:01:35 GMT

You can Try :

Count({[EagleFeedback.Win/Loss]-={'*'}>} [EagleFeedback.Win/Loss) (or -={*})

Re: Set Analysis - Counting NULL Values

lars_plenge — Fri, 22 Aug 2014 10:12:28 GMT

You can use:

count({$<Customer={"=isnull(Address)"}>} Customer)

Re: Set Analysis - Counting NULL Values

Fri, 22 Aug 2014 18:07:35 GMT

In this link i found the solution, it worked for me.

How to handle NULL values, ANDs and ORs in SET ANALYSIS: | QlikView Help, Tips & Hints

Re: Set Analysis - Counting NULL Values

antoniotiman — Fri, 22 Aug 2014 21:37:01 GMT

Hi Dave,

what is the origin of your data ?

DB or Load ?

Re: Set Analysis - Counting NULL Values

jaimeaguilar — Fri, 22 Aug 2014 21:55:04 GMT

Hi,

in my experience I can tell you that probably the only option with set analysis that will work for you is similar to what the blog suggested by Eugenio says:

Count({1 - <[EagleFeedback.Win/Loss]={'*'}>} [EagleFeedback.Win/Loss])

Otherwise, you can use NULLASVALUE in script in order to assign a string/numeric default value for nulls:

NULLASVALUE [EagleFeedback.Win/Loss];

Set NullValue = 'N/A';

regards