Different Results - NOT IsNull(Field) vs len(Field) > 0

[#1]

Hi All,

I am getting different results with NOT IsNull and Len functions

DATA_TAB:

load * inline

[ID,VAL

1,10-VAL

2,20-VAL

3,

4,40-VAL

5

6,''];

REF_TAB:

load * inline

[key, NAME,DT

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,

,EMPTY Value,9/9/2013];

left join (DATA_TAB)

load

    key as VAL,

    min(DT) as MIN_DT

Resident

     REF_TAB

where

len(key) > 0 /*----- Working */

    //NOT isnull(key)  /*------ NOT Working */

group by

    key

;

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?

Thanks,

AP

[Brice-SACCUCCI]

Hi,

please review this blog article :

http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/30/null-the-invisible-nothing

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:

load

     ID,

     if(len(VAL) = 0, null(), VAL)     as VAL

inline

[ID,VAL

1,10-VAL

2,20-VAL

3,

4,40-VAL

5

6,''];

Regards,

Brice

[#3]

Brice,

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"

Thanks,

AP

[Brice-SACCUCCI]

Sorry, I edited my last answer meanwhile.

[Brice-SACCUCCI · Accepted solution]

Check this one :

Inline load & null values

[#6]

Thanks Brice!

You are correct

Tried with the Set NullInterpret = '';  and worked. So it was , as you had mentioned, it was 0 length string.

Thanks,

AP

[Brice-SACCUCCI]

Great!

[maxgro]

I think

- 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

DATA_TAB:

load * inline

[ID,VAL

1,10-VAL

2,20-VAL

3,

4,40-VAL

5

6,''];

REF_TAB:

load if(len(key)=0,null(),key) as key, NAME,DT;

load * inline

[key, NAME,DT

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,

,EMPTY Value,9/9/2013];

left join (DATA_TAB)

load

    key as VAL,

    min(DT) as MIN_DT

Resident

     REF_TAB

where

//len(key) > 0 /*----- Working */

NOT isnull(key)  /*------ Working  */

group by key;