Qlik Community

Ask a Question

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
Henric_Cronström

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à!

 

Customers.png

 

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!

 

Shoes.png

 

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.

 

Button.png

 

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

 

HIC

19 Comments
Not applicable

Henric,

why didn't QlikTech make NULLs searchable? In QVD files NULLs have own code like any other field dictionary symbol and therefore are perfectly searchable. I assume that internal data representation in QllikView mirrors QVD so NULLs should be searchable in QlikView apps too. I'm pretty sure NULLs are not excluded from the bit-stuffed index.

PS. Life would be a bit easier for developers if they could write something like

Count( {< Orders=null() >} Customers)

or have an option of explicitly selecting NULL in a listbox

PPS. Excellent whitepaper, btw

6,608 Views
Henric_Cronström

On one hand, you have a point: True NULLs could be stored in the symbol table so they could in principle be made visible and searchable.

But on the other hand...

  • From a principal point, NULLs should not be treated as values. They should be hidden. They are not values.
  • It wouldn't solve the problem, since Missing values cannot be stored in the symbol tables. And missing values are really the most common type of NULL. So if true NULLs would be visible, but missing values not, I think we would have an even more confusing situation...

HIC

Explanation:

True NULL = The record exists, but the field is marked as lacking value.

Missing value = The record is missing, so there is no cell that can be marked NULL. What you have in the order table for customers that have not placed orders.

6,608 Views
Not applicable

I see your point. Yes, I agree -- it can be even more confusing, indeed.

0 Likes
6,608 Views
rbecher
MVP & Luminary
MVP & Luminary

But NULLs are not really stored in the symbol tables. The pointer in the record is just negative and so it points to nowhere.

0 Likes
6,608 Views
Henric_Cronström

The discussion is really hypothetical - could we change QlikView so that NULLs are searchable and selectable? The answer is probably yes. But such a solution would never cover the missing values, and then the solution isn't interesting.

HIC

6,608 Views
Not applicable

Hi Henric,

  How the load command script function NULLASVALUE change this rule ?

0 Likes
6,608 Views