Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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

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?

1 Solution

Accepted Solutions
Employee
Employee

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

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

4 Replies
Employee
Employee

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

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

Not applicable

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

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 ;-)

Not applicable

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

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.

Employee
Employee

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

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

HIC

Community Browser