Qlik Community

QlikView Documents

Documents for QlikView related information.

NULL handling in QlikView

NULL handling in QlikView

  • 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 = {}>

Labels (1)
Comments
Not applicable

This is an excellent write-up HIC, thanks!

0 Likes
khadeer_sparks
Valued Contributor

Good one. It helps more. Thanks

0 Likes
jldengra
Contributor

Thank you for this document.

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

0 Likes
MVP
MVP

Neat!

Thank you, Henric

0 Likes
johnpaul
Contributor

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.

0 Likes
Luminary
Luminary

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

Cheers,

DV

0 Likes
jcarpenter9
Contributor
0 Likes
MVP
MVP

Great post - very useful guide

Jonathan

0 Likes
prieper
Honored Contributor II

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

0 Likes
Not applicable

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

0 Likes
Version history
Revision #:
2 of 2
Last update:
‎12-19-2018 05:56 AM
Updated by: