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

[Anonymous]

Henric, I ran this script in Qlik Sense Desktop 3.1 SR3:

<Start of script>

Set NullInterpret = '';

NullPropagation:
Load *, X & Y as [X & Y], Text(X & Y) as [Text(X & Y)];
Load X, Y
Inline [
X, Y
,
0, 0
0, 1
1, 0
1, 1
, 0
, 1
1,
0,
] (delimiter is ',');

Set NullInterpret =;

<End of script>

This is the result I get, the last 4 records don't make sense, values in field [X & Y] appear to be invalid, it also appears that in order to have Null properly concatenated with a numeric, I'll always have to use Text function to wrap X & Y, Text(X & Y):

X,Y,X & Y,Text(X & Y)

,,,

0,0,00,00

0,1,01,01

1,0,10,10

1,1,11,11

,0,00,0

,1,01,1

1,,01,1

0,,00,0

[Anonymous]

Henric, can you please take a look at the sample loading script above which produces unexpected results.

[#4]

Thanks Henric, fantastic post!

[paulyeo11]

Hi All

I have a question related to this topic.

Re: How to drill in missing value ?

Hope some one can share with how to drill in the null value.

Paul Yeo

[luismadriz]

Very helpful and informative. Many thanks!

[alis2063]

Thanks for this post make so easier with exploring all the points of nulll...

[sergio0592]

Thanks!