Finding NULL

[hic]

NULL is not a value. It is a lack of value. It is a placeholder that marks nothingness.

So how do you search for NULLs? How do you find the customers that didn't buy product X? Or, how do you find the users that didn't log on this month? There is no search string that matches NULL and even if there were, you can’t select NULL.

NULLs cannot be selected explicitly, so to find the records with NULLs, the selection must always be made in another field. In the example of customers not having bought product X, it means that the Product field for some customers is NULL. Hence, you need to select the customers for which the Product is NULL.

In other words – you need to make the selection in a field other than where you have the NULL. And here’s how you do it:

1. Set your selection criteria the normal way.
2. Use Select Excluded on the field where you want to negate the selection

For example, if you want to find customers that have not bought Basket Shoes, then you should first select Basket Shoes from the Product list box. Then you will in your Customer list box have the customers that indeed bought Basket Shoes. But the grey customers are the ones you are looking for. So, right click, and Select Excluded. Voilà!

The second example was how to find users that have not logged this month. Analogously, you first select the month and then you negate the selection by using Select Excluded on the User list box.

A third example could be that you want to find the customers that have not bought any product at all. Then you should first right-click the products and Select All. This will maybe not change very much, but it will exclude the customers that never placed any orders. In other words: These are now gray and can be selected using Select Excluded.

A final example could be that you have a combination of criteria, e.g. you want to find customers that have not bought any shoes in the last few months. The method is still the same: Select relevant products and select relevant time range. The possible customers are the ones that have bought of the products in the time range, and the excluded customers are the interesting ones. Select Excluded!

However, when you have a combination of selections, QlikView doesn’t always remove both of the initial selections when you select the excluded values, so to get it right you should combine it with a Clear Other Fields. A good, user-friendly solution is to put both commands in a button that you label Select Excluded Customers.

If you want to read more about how to manage NULLs in your QlikView application, you should read this Technical Brief.

HIC