NULL handling in QlikView

[hic]

• What is the difference between a NULL and a missing value?
• How are NULLs propagated in expressions?
• How does QlikView display NULLs?
• How do I make NULLs selectable?
• How do I search for NULLs?
• Can NULLs be used in key fieds to link tables?
• What is Ternary Logic?

These quesions and others are answered in this Technical Brief.
Thank you, mellerbeck and Matthew Fryer for idea and inspiration.

See also

The Importance of Nothing ,

NULL – The Invisible Nothing and

Finding NULL

HIC

2012-12-18: Fixed an error in the section about NULL propagation pertaining to relational operators. Added examples in the same section. /HIC

2012-12-20: Added information about ideographic space. Changed layout of some truth tables (images). /HIC

2013-04-46: Added section about How QlikView displays NULLs

2016-10-13: Corrected a sentence about the result of <Product = {}>

[#2]

This is an excellent write-up HIC, thanks!

[khadeer]

Good one. It helps more. Thanks

[jldengra]

Thank you for this document.

It helps acquire a better understanding about how to deal null values in QV.

[swuehl]

Neat!

Thank you, Henric

[johnpaul]

Good document; solves a lot of data issues.

But, one thing that always gets me stuck is how to deal with a drill down (eg: you create a drill down group dimension)  through a NULL value.

[IAMDV]

Excellent post HIC. We need more of this type. Many thanks for this one.

Cheers,

DV

[jcarpenter9]

Corrected blog link:

http://community.qlik.com/blogs/qlikviewdesignblog/2012/06/28/the-importance-of-nothing

[jonathandienst]

Great post - very useful guide

Jonathan

[prieper]

Nice whitepaper, thanks for sharing.

In this context it might be worth to look at the "OTHERSYMBOL"-functionality, when using a mapping-table.

Imagine a simple script like:

Orders: LOAD CustomerID, OrderID, .... FROM ...;

Customers: LOAD CustomerID, CustomerName, CustomerRegion, ... FROM ...;

The usual process for the users would be to select a customer by region or by name and to check on the transactions. If - for whatever reason - there are entries in the Order-table with a customer-ID, which is not mapped, it would usually not be visible.

Changing the script to

LET OTHERSYMBOL = +;

Orders: LOAD CustomerID, OrderID, .... FROM ...;

Customers: LOAD CustomerID, CustomerName, CustomerRegion, ... FROM ...;

MissingCustomers: LOAD * INLINE [CustomerID, CustomerName, CustomerRegion, ...

+, #not mapped#, #not mapped#, ...];

will allow the users to select specifically all customers, which were not mapped.

The Inline-table must have the same fields as the "Customer"-table in order to be concatenated to it and works also with the LEFT KEEP-syntax, which we actually prefer for the mapping-tables in our applications.

Peter

[#11]

This is one of the most useful and well written documents I've read on a complex aspect fo QlikView functionality.