Qlik Community

QlikView Documents

Documents for QlikView related information.

NULL handling in QlikView

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

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
MVP & Luminary
MVP & Luminary

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

Thank you!

-Rob

0 Likes
Employee
Employee

Agreed. Exceptionally helpful. Thank you.

0 Likes
MVP & Luminary
MVP & Luminary

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

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

HIC

murozel76
New Contributor III

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,

0 Likes
Not applicable

Its a great document, thanks to HIC

0 Likes
Partner
Partner

Excellent Post by HIC, Thanks

0 Likes
brindlogcool
Contributor III

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

0 Likes
Not applicable

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

0 Likes
Not applicable


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

0 Likes
Version history
Revision #:
2 of 2
Last update:
‎2018-12-19 05:56 AM
Updated by: