Qlik Community

Qlik Design Blog

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

NULL – The Invisible Nothing

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

 

So, how does QlikView show the concept of nothing – when this is the relevant answer to the user’s click?

To investigate this, I will use a hypothetical database with two tables: Customers and Orders. The Customers table is a list of customers, and the Orders table is a list of orders that these customers have placed.

 

List boxes

In the picture below, you have a selection of two customers that haven’t placed any orders, i.e. they exist in the Customers table but there are no corresponding records in the Orders table. As a result, all values in the OrderID list box are gray.

List box NULL.png

In other words; for a list box, it is simple: NULL is not visible as an explicit list box entry. If all entries are marked as gray, then the empty set is the answer to the click, i.e. NULL is the answer.

 

If you have a list box where you have a visible entry that is blank; that you can click on; that can be selected, then it is not a NULL. NULLs are never visible in list boxes and can never be selected. Instead, you have an empty string or some kind of white space.

 

Table boxes

In a table box, as well as in all other places where a NULL can occur, e.g. labels, text boxes, buttons, etc., NULLs are displayed as dashes. In these places, NULL is visible but not selectable.

Table box NULL.png

 

Chart dimensions

For a chart, it becomes more complicated. First of all, a NULL can occur either as a dimensional value or in the measure - the expression. These are two very different cases and should not be confused. Further, the two cases are managed in two different places in the chart properties.

 

With the above data, it would be reasonable to make a chart that shows sales per customer. If there are orders that are not attributed to any customer, then you will get a NULL in the dimension of the chart – a NULL which is displayed as a dash. Below you can see that order nr 10874 has no customer associated:

Chart dimension NULL D.png

If you don’t want to show lines with NULLs in the dimension, you can suppress these on the Dimensions tab in the chart properties.

 

Chart measures

If your data is the other way around, i.e. you have customers that are not attributed to any orders, you will for these get NULLs in OrderID and Amount. However, the Sum() and Count() functions still return zero, which is correct, since Sum(NULL) is zero. Other aggregation functions such as Only() may return NULL, though, and this will be displayed as a dash.

Chart expression NULL D.png

If you don’t want to show lines with zeros or NULLs as expression value, you can suppress these on the Presentations tab in the chart properties.

 

So, the bottom line is that NULLs are sometimes visible, sometimes not. Sometimes they are displayed as dashes, and when aggregated, usually as zeros. But they are never selectable.

 

And with this, I hope that the mystery around NULLs is somewhat less of a mystery.

 

HIC

 

PS. In this blog post, I use “NULL” to denote both real NULLs and missing values. What the difference is? Read this (updated) white paper and find out. There you can also read about how to make NULLs both visible and selectable.

3 Comments
Not applicable

a blog entry on how to FIND NULL relations would be interesting.

f.e. find users which never logged in this month or didnt buy anything.

ony way would be to use variables for the time instead of an calendar and check. If from the list of userIDs, one ist not listetd at the orders in $vMonth, its missing.

0 Likes
1,795 Views
0 Likes
1,795 Views
Luminary
Luminary

Though I am too late to comment on this  but wonderful article, thanks HIC

0 Likes
1,795 Views