Qlik Community

QlikView Documents

Documents for QlikView related information.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.

NULL handling in QlikView

Employee
Employee

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!

khadeer_sparks
Valued Contributor

Good one. It helps more. Thanks

jldengra
Contributor

Thank you for this document.

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

MVP
MVP

Neat!

Thank you, Henric

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.

Luminary
Luminary

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

Cheers,

DV

jcarpenter9
Contributor
MVP
MVP

Great post - very useful guide

Jonathan

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

Not applicable

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

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