topic set analysis function in QlikView

set analysis function

tizianacaem — Mon, 27 Sep 2010 15:23:01 GMT

Hi,

i want to say if is possible to test, with set analysis syntax, if the value in a field is null.

thanks

set analysis function

Mon, 27 Sep 2010 15:25:41 GMT

I´m not sure...

but something like this!!!

sum({<Region =-{} >} Sales)

set analysis function

tizianacaem — Mon, 27 Sep 2010 15:35:05 GMT

Unfortunately no!

In this way it shows no selection in the Region field.

What I need is the selection for record wich have a value null in the field.

Thanks

set analysis function

Anonymous — Mon, 27 Sep 2010 16:09:43 GMT

Is it necessary to use set analysis? This expression should work just fine:
sum(if(isnull(Region), Sales))

set analysis function

Mon, 27 Sep 2010 16:13:02 GMT

According to the Reference Guide, it should be <FIELD = {}> or <FIELD = {'A value that doesn't match anything'}>. I haven't really had a lot of success with those methods. When I've comes across the need for this, I usually replace the Null value with something else. For text, I replace the null value with a dash, which makes it easier to use in Set Analysis and it allows for nulls to be selected in a List Box (if the user knows dash equals null).

set analysis function

Mon, 27 Sep 2010 16:18:19 GMT

Hi,

I don't think theres a way to filter null values within a field using set analysis. However you might be able to get the null values inderectly depending on your applications needs. For example lets say you have the following tables:

LOAD * INLINE [
OrderID, Amount
1, 100
2, 250
3, 300
];
join
load * inline [
OrderID, Product
1, 1
2, 1
];

Notice OrderID = 3 does not have any Product associated, in this case you can manage QlikView to display a table containing all the OrderID's with no associated product. To accomplish these, create a table with OrderID as dimension and the following expression:

sum(Amount) - sum({$<Product = {"*"}>} Amount)

Hope this helps.

Regards

set analysis function

Miguel_Angel_Baeyens — Mon, 27 Sep 2010 16:28:50 GMT

Hello,

I agree to NMiller and Michael as preferable solutions. Anyway, I did sometimes

SUM({1 - < NAME = P(NAME) >} AMT)

Regards.