Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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
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 😉
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.
Your'e not alone... I always need to go back and check how things work - even when I wrote the documentation myself.
HIC