NULL handling in QlikView

[hic]

• 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 = {}>

[rwunderlich]

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

Thank you!

-Rob

[JonnyPoole]

Agreed. Exceptionally helpful. Thank you.

[rbecher]

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

[hic]

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

HIC

[murozel76]

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,

[#7]

Its a great document, thanks to HIC

[Qlik__Vinodh]

Excellent Post by HIC, Thanks

[brindlogcool]

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

[#10]

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

[#11]

Henric,

is there a possibility to demonstrate, how to make a field value '-' clickable?

As you described in your document NULL and Nothing, missing values can occur if in related table there are no according records. One example:

Stuff:

load * inline [

StuffNr, valid_from, valid_to, Sex

00001, 01.01.2013, 31.12.2013, male

00002, 02.05.2013, 24.11.2013, female

]

Pregnancy:

load * inline [

StuffNr, valid_from, valid_to,  pregnant

00002, 03.05.2013, 22.11.2013, YES

So if user will see the field pregnant, it will be just only displayes YES and nothing more.

How to create for missing records (all men and women which weren't pregnant) a row containing NO? Is there any other way to beat this issue?

Now it is impossible to select all non-pregnant stuff.

Thank you

Regards

Stan