topic How to filter NULL in set analysis in QlikView

How to filter NULL in set analysis

Sat, 12 Sep 2009 02:47:43 GMT

is there a way to filter a null value in set analysis?

Say I have the following values:

Family_Id	Amount
1	1368316
2	436479
5	18706
4	797
-	64367663

I want to display in one column the sum of families 1,2,5,4 (no problem with that) but I can't figure out a way to filter for the null family_id value (Family_Id-={1,2,4,5} is not working)

How to filter NULL in set analysis

Sat, 12 Sep 2009 02:58:11 GMT

never mind, I solved it with

sum

(Amount)-sum({<Family_Id={*}>} Amount)

How to filter NULL in set analysis

Sat, 12 Sep 2009 03:42:42 GMT

Your method is fine, but you can also use an empty element set and exclusion.

Sum({<Family_Id-={''}>} Amount

How to filter NULL in set analysis

Mon, 14 Sep 2009 21:21:55 GMT

Hi NMiller,

I gave this a try, but it's returning no values. I'm guessing this is checking for an empty not null value?

How to filter NULL in set analysis

Tue, 15 Sep 2009 03:53:06 GMT

The expression I gave should Sum non-null values of Family_Id. It is working when I test it on your data in an inline load (leaving the Null Family Id as a blank). The funny thing is, I always get 0 using your expression.

I attached the sample that I am using. I'm not sure why we're getting different results. Anyway, you got it working on your end and that's what counts.

How to filter NULL in set analysis

Tue, 15 Sep 2009 03:54:51 GMT

Sorry, double-post. I'm getting those server timeout errors again, so the attachment won't post (but the message does).

How to filter NULL in set analysis

Tue, 15 Sep 2009 07:19:45 GMT

I'm sorry, I should probably point out that I'm trying to filter a missing value, not a null value

How to filter NULL in set analysis

Anonymous — Tue, 15 Sep 2009 10:06:07 GMT

The blank value in an inline load returns a blank string and not a proper null value. When loading an empty cell from Excel for example this will be treated as a proper null.

Of course you can do a second pass with a preceding load on an inline load and assign null() if the length is 0 in the field.

How to filter NULL in set analysis

Wed, 16 Sep 2009 02:07:59 GMT

Thanks for the info Johannes. I usually use Inline Loads when testing out my ideas before posting. I'll have to consider that in the future.