Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER

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
jcarpenter9
Partner - Creator
Partner - Creator

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.

empty_set_test.png

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

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

Hi Henric,

thanks for clarifying.

What would be a use case for


<Product = {} >

?

hic
Former Employee
Former Employee

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
Partner - Creator
Partner - Creator

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
Creator III
Creator III

good explanation

Not applicable

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]

idname
1NULL
2NULL
3NULL
4
5
6
7NULL
8NULL

.

When I query the above table with where condition

SELECT [id]

      ,[name]

  FROM [test_null]

  where name <>'aa'

Output for above Query in SQL:

idname
4
5
6

** 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  -
4a
5b
6c
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
Former Employee
Former Employee

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."

Equality truth tables.png

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

HIC

Not applicable

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

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

HIC

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