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]

Really helpful and found it interesting while  reading.

[#3]

Hello --

If you happen to come across this post and are still having trouble getting the IF() examples in the PDF working and are using mySQL as your data source I'd like to direct to a possible solution courtesy of ANTONIO MANCINI.

[jcarpenter9]

Pardon me for cross-posting, but I just started a discussion about a null-related problem here:

http://community.qlik.com/message/677925

I found an issue where set modifiers do not perform as I expected when searching for a value in a column that contains all nulls. In this case, the set analysis expression returns ALL rows, instead of no rows. But when the columns contains even one row with a non-null value, the set analysis returns zero rows as expected. An example .qvw is attached to the discussion post.

- James

[#5]

Great Post. Thanks!

Regards,

Kuldeep

[Anonymous]

Hi HIC,

Can you please the help me to exclude the Null values and zero values from Bar chart.

It commute like as follow

I have a chart with Dimension as Material(A).The Expression are Consumption(B) and Consumption Limits(C).

Now I don't have all the Consumption limits(C) (It is INPUT FIELD it will provided now and then when available) available readily for all the Material(A).

I should show only materials(A) which has Consumption limits others shouldn't be visible in Bar chart.

When the consumption limits(C) is available then we can show the other Materials(A).

In other words Material (A) which doesnt have Consumption limits(NULL or Zero) should be not shown in chart

What formulae or calculated dimension should be written in the chart.

[hic]

Can you not use "Suppress zero values" in the properties of the chart?

[puttemans]

this document helped me out a few times already.

THX

[dinicholls]

As a person who unfortunately has to suffer with lots of nulls / blanks / and missing data, this has helped me a lot!

Thanks!

[#10]

Nice post! Still got one question. Lets say you Set NullInterpret = ' ';

How can you set it back to Qlik's default value? So Qlik doesnt interpret ' ' as a Null anymore further in the script.

[hic]

I haven't tried it, but I suspect a

   Set NullInterpret = ;

will do the trick.