set analysis function

[tizianacaem]

Hi,

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

thanks

[#2]

I´m not sure...

but something like this!!!

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

[tizianacaem]

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

[Anonymous]

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

[#5]

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).

[#6]

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

[Miguel_Angel_Baeyens]

Hello,

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

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

Regards.