Discussion Board for collaboration on QlikView Scripting.
I am getting different results with NOT IsNull and Len functions
load * inline
10-VAL, Value of 10, 1/12/2014
10-VAL, Value of 10, 1/1/2010
10-VAL, Value of 10, 12/12/2010
20-VAL, Value of 20,2/1/2014
20-VAL, Value of 20,10/1/2014
20-VAL, Value of 20,
left join (DATA_TAB)
key as VAL,
min(DT) as MIN_DT
len(key) > 0 /*----- Working */
//NOT isnull(key) /*------ NOT Working */
Here are the results
Using Len(key) > 0 , it does NOT load the nulls
But while using NOT isnull(key) , it loads the nulls as well
Does anyone know what could be wrong?
Go to Solution.
Check this one :
Inline load & null values
please review this blog article :
Also, I guess your values are not "null" with the inline LOAD, but rather are strings with a length of 0. Try to add an expression with the formula len(VAL) on your table to get a better understanding of what's happening.
Try this also:
if(len(VAL) = 0, null(), VAL) as VAL
Thanks for the pointer, but the issue is qlikview is NOT ignoring the null values and establishes a join thru null value when we use "NOT IsNull"
Sorry, I edited my last answer meanwhile.
You are correct
Tried with the Set NullInterpret = ''; and worked. So it was , as you had mentioned, it was 0 length string.
- in REF_TAB last value (red) of key is not null
- the where condition NOT isnull(key) is true for last value of key
- the group by will return an empty, 9/9/2013 for last record of REF_TAB
- this empty, 9/9/2013 will join to empty VAL in DATA_TAB
try to add the bold
load if(len(key)=0,null(),key) as key, NAME,DT;
//len(key) > 0 /*----- Working */
NOT isnull(key) /*------ Working */
group by key;