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

[#2]

Very useful!

One thing took me quite a while to sort out:

The meaning of NULL as a result of a Conditional function of an expression.

According to the blog post On Boolean Fields and Functions of Henric Cronström:

Further, in situations where QlikView expects a Boolean, e.g. in the first parameter of the if() function or in a Where-clause, QlikView will interpret 0 as FALSE, and all other numbers as TRUE.

My conclusion:

NULL is different from 0 and therefore in this context TRUE and the expression condition fulfilled.

[rbecher]

Hi Thomas,

I think your conclusion is not correct. Just consider the result of: =If(Null(),1,2)

NULL should never be TRUE.

- Ralf

[swuehl]

NULL is not a number 😉

[hic]

Both Ralf and Stefan are spot on. I wrote "all other numbers as TRUE" - but this does not include NULL. NULL is not a number. It is not even a value. It is a lack of value.

HIC

[#6]

Ok, so far so good.

Then let's assume we have a text object with a show conditional layout option:

=NULL()

The text object remains visible.

In the QV11 reference manual it's stated for the show conditional of text objects/listboxes:

... The sheet object will only be visible when the condition returns TRUE.

So, as NULL is a lack of value and should never be true - shouldn't it be written:

... The sheet object will only be visible when the condition returns not FALSE

or otherwise the object shouldn't be visible in case of a show conditional function returning NULL.

Thank you for further clarification.

[rbecher]

Right, this seems to be an issue. In this case it is helpful to wrap the expression to catch NULL:

=if(<expression>, -1, 0)          // works for =if(Null(), -1, 0)

[hic]

Well, in principle you're right: The object is displayed even though the expression is evaluated to NULL. So, the behavior seems to be inconsistent. But - and this is the main point - the object is shown even though the expression is empty, i.e. NULL. And I think we all agree that it should be like that.

In other words - I think it is OK that a show condition with an undefined value (i.e. NULL) is evaluated to "the object should be shown", even though NULL in an If() function is considered FALSE.

HIC

[rbecher]

But the expression is not empty, the result is. An empty string is also considered as FALSE in expressions:

eg. =if('', -1, 0)

[IAMDV]

I agree with HIC. There are 2 different things while dealing with NULLs. Although they are interdependent.

1. How to treat NULL values in QlikView – Rules are designed based on Kleene’s three valued logic. Which is well recognised standard of treating NULLs.
2. How NULL values are interpreted in QlikView – I think this aspect can be subjective to the product/tool. Because if we follow ANSI standard – NULLs should be sorted into one group as if they are equal (In true sense, they are not equal!). Some of the RDBMS systems group & sort NULL values as they are equal but you can’t “=NULL” but you would use “IsNull” to check the values.

I think treating NULLs should always have universal approach and interpreting NULLs can be subjective to the product/tool. I’m okay showing the text object with conditional layout as NULL().

Thanks,

DV

[#11]

The issue leading to the question was as follows:

I was looking for a condition showing my three chart expressions only if 'black', 'color' or 'black' AND ' 'color' were selected in a listbox.

=match(GetFieldSelections(BLACKCOLOR),'black','color', 'black, color')

As NULL is the result if  no selection is beeing made, the expression (or text object) remains visible. Although in my case this wasn't intended I can live with that.

Considering the proposition of Ralf Becher my expression has changed to:

=if(match(GetFieldSelections(BLACKCOLOR),'black','color', 'black, color'),-1,0)

Probably there are pros and cons for dealing with the problem one way or the other - it's just good to know the reason why.