topic Re: Null() is not equal to null() from peek()? in QlikView

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

Mon, 10 Feb 2014 12:17:50 GMT

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?

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

hic — Mon, 10 Feb 2014 12:53:52 GMT

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

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

Mon, 10 Feb 2014 13:14:58 GMT

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 😉

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

Mon, 10 Feb 2014 13:26:36 GMT

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.

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

hic — Mon, 10 Feb 2014 13:26:49 GMT

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

HIC