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

[jcarpenter9]

The syntax <Field={}> is not working for me to return an empty set, i.e. rows not associated with a value in that field as described in this post. I created a small data model with the script below, having null values in the Dim2 field. Yet the empty set syntax isn't returning values. Am I using it correctly, or has something changed in the calculation engine? The test app is running on v11.20.13314 SR14, 64-bit.

SET NullInterpret='';

Fact:

LOAD * INLINE [

    Dim1, Dim2, Val

    A, 1, 0

    A, 2, 1

    A, , 2

    B, , 3

    B, 2, 4

    B, 3, 5

    C, 1, 6

    C, , 7

    C, 3, 8

];

Dimension2:

LOAD * Inline [

     Dim2, Desc

     1, One

     2, Two

     3, Three

];

[hic]

No it has not changed. Rather, I need to correct the white paper, since it incorrectly implies that you can use this syntax to find records that aren't associated with any field value. But this is not possible. NULL is never selectable.

However, the white paper also (correctly) says:

"Note that the set modifier <Product = > is not the same as <Product = {} >. The former merely

removes the existing selection in the field, whereas the latter returns an empty set. "

HIC

[swuehl]

Hi Henric,

thanks for clarifying.

What would be a use case for

<Product = {} >

?

[hic]

I cannot see any use case for the explicit use of this.

However, a reasonable use case could be that you have <Product={$(=SomeExpression)}>. This means that it is possible that SomeExpression occasionally evaluates to an empty string. Then the appropriate evaluation of the Set Expression should be an empty set. So, I think it behaves correctly.

HIC

[jcarpenter9]

Ah, good to know. I used the search string <FieldA={"=NullCount(FieldB)>0"} to find rows not associated with a field value, but wondered why the ={} syntax didn't give the same result. Now I know.

Thank you for clarifying, Henric.

[pipuindia99]

good explanation

[#8]

Could anyone can help me with the behaviour of Null in  Qlikview and SQL Behaviour. Initially I had thought they both are same but today with simple experiment they both behaved differently.

SQL Script:

SELECT [id]

      ,[name]

  FROM [test_null]

id	name
1	NULL
2	NULL
3	NULL
4	a
5	b
6	c
7	NULL
8	NULL

.

When I query the above table with where condition

SELECT [id]

      ,[name]

  FROM [test_null]

  where name <>'aa'

Output for above Query in SQL:

id	name
4	a
5	b
6	c

** It is not showing id 1,2,3,7,8 though name ('aa') is not equal to Null

But the behaviour in Qlikview is different as follows

QLIKVIEW LOAD Script:

TEST_NULL_QlikView:

Load *

where name  <> 'aa';

SQL select * from test_null;

Output in Qlikview in TableBox

id	name
1	-
2	-
3	-
4	a
5	b
6	c
7	-
8	-

From both the Output, the SQL Server Engine with SQL Query, it is ignoring NULL Rows but in Qlikview it isn't. Could anyone can help me, If I am missing something.

Many Thanks in Advance

[hic]

That's correct.

This is described on page 7:

"The equality and inequality operators will return NULL if both operands are NULL. But if

only one of the operands is NULL, the comparison will always deny equality, i.e. the

equality operator will return FALSE and the inequality operator will return TRUE."

In other words: The Qlik engine will deny equality, and evaluate NULL <> 'x' to TRUE.

HIC

[#10]

Hi Henric,

I am glad for your response. So Qlikview and SQL behaves bit different though they both follow Three-Valued Logic.

In other words: The SQL engine will deny equality, and evaluate NULL <> 'x' to FALSE.

Could you help me, how to avoid synthetic keys while using role playing dimensions and Fact

table, I have seen few posts how to avoid use link tables, but that would be useful  if I have common fields in 2 Fact tables, but I want to use dimensions and Facts.

Eg:

FactsTables:

SalesAmount

Taxamount

SalesDate

ShippedData

OrderDate

DimensionTable:

Date

Year

Month

Date as SalesDate

Date as ShippedData

Date as OrderDate

I need to resolve this issue for multiple dimensions, not only for Date Dimension, I have seen your Canonical Calendar Post. It will be similar to that, but for other dimension I can't create common. With your Immense experience, could you help me, this would be useful to many people like me.

Many Thanks

[hic]

I suggest you post this question in a thread of its own. Then you will get many good suggestions. Also, read Canonical Date.

HIC