- Move Document
- Delete Document and Replies
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
NULL – The Invisible Nothing and
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 = {}>
- Move Comment
- Delete Comment
- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
This is an excellent write-up HIC, thanks!
- Move Comment
- Delete Comment
- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
Good one. It helps more. Thanks
- Move Comment
- Delete Comment
- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
Thank you for this document.
It helps acquire a better understanding about how to deal null values in QV.
- Move Comment
- Delete Comment
- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
Neat!
Thank you, Henric
- Move Comment
- Delete Comment
- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
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.
- Move Comment
- Delete Comment
- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
Excellent post HIC. We need more of this type. Many thanks for this one.
Cheers,
DV
- Move Comment
- Delete Comment
- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
- Move Comment
- Delete Comment
- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
Great post - very useful guide
Jonathan
- Move Comment
- Delete Comment
- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
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
- Move Comment
- Delete Comment
- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
This is one of the most useful and well written documents I've read on a complex aspect fo QlikView functionality.