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
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Thank you!

-Rob

JonnyPoole
Former Employee
Former Employee

Agreed. Exceptionally helpful. Thank you.

rbecher
MVP
MVP

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
Former Employee
Former Employee

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

HIC

murozel76
Contributor III
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,

Not applicable

Its a great document, thanks to HIC

Qlik__Vinodh
Partner - Creator II
Partner - Creator II

Excellent Post by HIC, Thanks

brindlogcool
Creator III
Creator III

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

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

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

Version history
Last update:
‎2018-12-19 05:56 AM
Updated by:
Former Employee