Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP

NULL handling in QlikView

cancel
Showing results for 
Search instead for 
Did you mean: 
hic
Former Employee
Former Employee

NULL handling in QlikView

Last Update:

Dec 19, 2018 5:56:17 AM

Updated By:

hic

Created date:

Jun 29, 2012 2:46:27 AM

Attachments
  • 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
Specialist
Specialist

Good one. It helps more. Thanks

jldengra
Creator
Creator

Thank you for this document.

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

swuehl
MVP
MVP

Neat!

Thank you, Henric

johnpaul
Partner - Creator
Partner - Creator

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
Luminary Alumni
Luminary Alumni

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

Cheers,

DV

jonathandienst
Partner - Champion III
Partner - Champion III

Great post - very useful guide

Jonathan

prieper
Master II
Master 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
Last update:
‎2018-12-19 05:56 AM
Updated by:
Former Employee