Null() is not equal to null() from peek()?

[#1]

Hi all,

Scripting:

I just found out that if you do an 'if' in the script on a column that has a Null() value and you compare that with a peek() value that results in Null(), then you will not get a match.

Simplified example below. The purpose of this script is to have a SEQ column that counts up each time when the Data column changes.

temp:
LOAD Line, Track, From, To, Length, if(Data='',null(),Data) as Data INLINE [
    Line, Track, From, To, Length, Data
    1, A, 0, 1, 1
    1, A, 1, 2, 1
    1, A, 2, 3, 1, Y
    1, A, 3, 4, 1, X
    1, A, 4, 5, 1, X
    1, A, 5, 6, 1, Y
    1, B, 1, 3, 2, X
    1, B, 3, 4, 1, Y
    2, C, 0, 10, 10, Z
    2, D, 0, 5, 5, X
    2, D, 5, 6, 1, Y
    2, D, 6, 7, 1, X
    2, D, 7, 9, 2, Y
    2, D, 9, 10, 1, X
];


definit:
LOAD *,
  if(RowNo()=1,1,if(Track=peek('Track',-1) and (Data=peek('Data',-1) or (isnull(Data) and isnull(peek('Data',-1)))),num#(peek('SEQ',-1)),num#(peek('SEQ',-1))+1)) as SEQ
RESIDENT temp;


DROP TABLE temp;

SEQ will increment for the first two records. I have solved this by using following if statement:

if(RowNo()=1,1,if(Track=peek('Track',-1) and (Data=peek('Data',-1) or (isnull(Data) and isnull(peek('Data',-1)))),num#(peek('SEQ',-1)),num#(peek('SEQ',-1))+1)) as SEQ

Is this normal behavior?

[hic · Accepted solution]

Yes, that is correct behaviour. See NULL handling in QlikView.

If you make a comparison with NULL, this will evaluate to FALSE or NULL. Never to TRUE.  So, basically, you cannot make comparisons with NULL.

HIC

[#3]

Thank you Henric for your fast response. Strangely, I have read that post and the PDF very thoroughly last summer. My memory is probably incapable of finding everything related to Null() when it needs to 😉

[#4]

Firstly we can't compare the Null value with another null value and work around, we can use Len and Trim function for Null comparison.

[hic]

Your'e not alone... I always need to go back and check how things work - even when I wrote the documentation myself.

HIC