How to handle NULL values​​, ANDs and ORs in SET ANALYSIS:

Many times we need to ask for null values ​​within an expression for what we use ISNULL () and its negation NOT ISNULL (). But how deal with null elements into a SET ANALISYS?

The SET ANALISYS is the programmatic equivalent of what a user does when select values ​​of the model. So the problem is, from a user point of view, try to SELECT a field with NULL value which is impossible since you can not “click” (select) on something that does NOT exist in the model ….!

The solution is to understand the whole model as a “set” and operate on it as such, ie using Boolean algebra.

Let’s begin:

Generate our model with Customers and Customer Complaints. As not all clients have claims to relate both tables get some null values​​:

Here the structure of the tables:

load * inline [
CustID, Anual_Sales, VIP
1 , 100, Y
2 , 200, Y
3 , 300, Y
4 , 400, N

Load * inline [
CustID, ClaimID, Claimed_Amount
1, 1, 5
1, 2, 8
4, 3, 15
4, 4, 1

And we get:





 Suppose we want to calculate the annual sales of customers with NO complaints, but how we ask for something that can not be mentioned, select and click (no value)?

Here’s the answer:


What we do is say “filter EVERYTHING (” * “), which has at least a claim” and then deny it, obtaining its complement (SUM({-<ClaimID={‘*’>} Anual_Sales)).

This works fine, but the issue can become a bit complicated when we test more complex conditions, here you have to understand that everything you put between <…> will be denied and when we put more than one condition separated by commas is as if were using ANDs but if we deny all may not be the desired result (eg: that ClaimID null and ….).

 To solve this we must understand that each <…> that appears in a SET ANALYSIS is a set in itself and certainly we can operate between sets:


Here (SUM ({- <ClaimID={‘*’}> * <Anual_Sales={“> 250 “}>} Anual_Sales)) ordered everything where ClaimID is null (the denial of any value) AND (symbol “*” stands for the intersection ) Anual_Sales> 250.

Finally, and similarly can operate and obtain ORs between sets using the “+”:



Acerca de pparnisari
Casi 44 vueltas al sol. Licenciado en sistemas. Curioso, investigador, excelente para resolver problemas prácticos. Casado, 2 hijos, cada vez con mas preguntas y respuestas mas en duda. Almost 44 laps to the sun. System engineer. Curious, researcher, great for solving practical problems. Married, 2 children, each time with more questions and more answers in doubt.

Deja un comentario

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de

Estás comentando usando tu cuenta de Cerrar sesión /  Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión /  Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión /  Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión /  Cambiar )


Conectando a %s

A %d blogueros les gusta esto: