topic IsNull returns Null if value is Null in QlikView

IsNull returns Null if value is Null

Wed, 20 Jul 2011 07:46:16 GMT

I'm getting strange behaviour from the IsNull() function. If there is a value it gives me 0. But if it's Null, the function returns Null and not -1.

If I do a Len() on the value, I also get Null where there is no value, so there really is no value.

I get this behaviour on both 64bit and 32bit.

Any help?

Thanks,

Brian

kaushiknsolanki — Wed, 20 Jul 2011 07:48:10 GMT

Hi,

Can you upload the qvw file.

Regards,

Kaushik Solanki

Wed, 20 Jul 2011 09:47:28 GMT

Further discovery -

I'm using the IsNull() to apply a rule to the data and join the result of the function to the same table that has the field value that I'm checking.

If I try to join the field to the table as follows, I get the failure I described:

Left Join (TABLE_A)

Load

KEY_FIELD,

IsNull(VALUE_FIELD) as NullCheck

Resident (TABLE_A)

But, if I make it a whole new table, it works as I expect it to:

NEW_TABLE:

Load

KEY_FIELD,

IsNull(VALUE_FIELD) as NullCheck

Resident (TABLE_A)

What small peculiarity am I missing?

Tks,

Brian

PS: Thanks, Kaushik, I will upload a version when I get a chance.

erichshiino — Fri, 22 Jul 2011 22:32:09 GMT

This is strange...

What happens if you right this:

Left Join (TABLE_A)

Load

KEY_FIELD,

if( IsNull(VALUE_FIELD),0,1) as NullCheck

Resident (TABLE_A)

Left Join (TABLE_A)

Load

KEY_FIELD,

if( len(VALUE_FIELD),1,0) as NullCheck

Resident (TABLE_A)