article NULL handling in QlikView in Member Articles

NULL handling in QlikView

hic — Wed, 19 Dec 2018 10:56:17 GMT

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.

Re: NULL handling in QlikView

Fri, 29 Jun 2012 07:58:04 GMT

This is an excellent write-up HIC, thanks!

Re: NULL handling in QlikView

khadeer — Fri, 29 Jun 2012 12:11:35 GMT

Good one. It helps more. Thanks

Re: NULL handling in QlikView

jldengra — Mon, 02 Jul 2012 10:34:55 GMT

Thank you for this document.

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

Re: NULL handling in QlikView

swuehl — Wed, 04 Jul 2012 22:18:21 GMT

Neat!

Thank you, Henric

Re: NULL handling in QlikView

johnpaul — Fri, 20 Jul 2012 15:23:50 GMT

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.

Re: NULL handling in QlikView

IAMDV — Sat, 04 Aug 2012 16:59:14 GMT

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

Cheers,

Re: NULL handling in QlikView

jcarpenter9 — Fri, 17 Aug 2012 15:02:09 GMT

Corrected blog link:

http://community.qlik.com/blogs/qlikviewdesignblog/2012/06/28/the-importance-of-nothing

Re: NULL handling in QlikView

jonathandienst — Sun, 02 Sep 2012 12:44:37 GMT

Great post - very useful guide

Jonathan

Re: NULL handling in QlikView

prieper — Thu, 22 Nov 2012 10:24:35 GMT

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

Re: NULL handling in QlikView

Mon, 26 Nov 2012 10:45:06 GMT

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

Re: NULL handling in QlikView

rwunderlich — Wed, 28 Nov 2012 01:24:02 GMT

Exceptional paper. It cleary explains many important concepts and behaviors.

Thank you!

-Rob

Re: NULL handling in QlikView

JonnyPoole — Wed, 19 Dec 2012 16:49:22 GMT

Agreed. Exceptionally helpful. Thank you.

Re: NULL handling in QlikView

rbecher — Wed, 19 Dec 2012 20:36:25 GMT

Great document. I'll collect all posts from Henric to build up a knowledge base!

I just want to add something to the paragraph "How to test for NULLs and missing values?"

There are some more (white) spaces and blanks if you have to deal with Unicode:

http://community.qlik.com/docs/DOC-2365

Re: NULL handling in QlikView

hic — Thu, 20 Dec 2012 08:55:01 GMT

Thank you for this information, Ralf. I have changed the document accordingly.

HIC

Re: NULL handling in QlikView

murozel76 — Mon, 24 Dec 2012 08:28:05 GMT

A very useful post, thank you for sharing. I have saved the document for reference.

By the way, we applied a method similar to one of the alternatives in the document: We detected the null values by using the "isnull()" function and then replaced them with the string "null" in the load script. This may lead to some different results in some calculations, but since our field of interest was a string variable and what we needed was just to be able to select the null values explicitly, it has worked.

Regards,

Re: NULL handling in QlikView

Tue, 30 Apr 2013 10:08:32 GMT

Its a great document, thanks to HIC

Re: NULL handling in QlikView

Qlik__Vinodh — Sat, 20 Jul 2013 06:20:49 GMT

Excellent Post by HIC, Thanks

Re: NULL handling in QlikView

brindlogcool — Thu, 01 Aug 2013 17:17:07 GMT

Do NULL Vlaues or Blank spaces will impact the file size?

Re: NULL handling in QlikView

Fri, 02 Aug 2013 07:47:25 GMT

Hi Henric,

I like your document "Null and Nothing".

I have now compared changes between version of Apr 26, 2013 (new) and version of Dec 20, 2012 (old) of this document and I have found that maybe not all changes were really intended.

For example chapter "Introduction" terminates with paragraph

In the example shown in the picture, there are amounts for all quarters in 2011 but not for Q3 and Q4 2012. When the pivot table is calculated, an algorithm loops over all records in the database. Since there are no data records for the last two quarters, the consequence is that the expression is never calculated for these cells (middle and rightmost tables). So basically it looks as if IsNull() has returned NULL, when it in fact has not been calculated at all.

But there is no picture in the new version. This picture was in old version - at the end of chapter "Introduction" and together with some explanation paragraph.

Can you please compare your old and new versions and add to the current version some blocks of text and/or pictures from the old versions?

The reason of this my appeal is that there are more similar differences between these old and new version, where some interesting parts of text from old version were deleted in new version.

Thank you very much for your great text!

Mirek