Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry

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
Not applicable

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

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

NULL is not a number 😉

hic
Former Employee
Former Employee

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

Not applicable

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

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

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

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

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

IAMDV
Luminary Alumni
Luminary Alumni

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

Not applicable

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.

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