Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report

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