topic Multiple Values for SET NullInterpret? in QlikView

Multiple Values for SET NullInterpret?

dselgo_eidex — Thu, 28 Jan 2016 15:37:28 GMT

Hey, I'm trying to figure out how to set multiple values for NullInterpret. The Excel document I'm loading from is using - and . to display null values and I want these to be loaded in as NULL. I'm also wondering if the expression has to be enclosed by single quotes or not ('.', '-'). I would expect it to look like this but I'm not sure if QV wants it this way:

SET NullInterpret = '.', '-';

Please do not link the help text for NullInterpret, I've read it and it does not answer my question.

Re: Multiple Values for SET NullInterpret?

swuehl — Thu, 28 Jan 2016 20:05:26 GMT

NullInterpret is a variable, which can't hold an array of values. So what you are trying to do is not possible, IMHO.

Re: Multiple Values for SET NullInterpret?

Oleg_Troyansky — Thu, 28 Jan 2016 20:19:22 GMT

As a work around, you could use REPLACE() functions for your multiple symbols to replace all of them with a consistent character, like '-', and then use that single character in the NullInterpret variable.

Cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Milan, Italy!

Re: Multiple Values for SET NullInterpret?

HirisH_V7 — Thu, 28 Jan 2016 20:34:02 GMT

Hi,

Check this,

Data:
LOAD * ,
If(IsNum(Value),Value,Null()) as Value1,
If(IsNum(Value),Value,Null()=0) as Value2,
If(Value='.' or Value='-' or Value='+',Null(),Value) as Value3,
If(Value='.' or Value='-' or Value='+',Null()=0,Value) as Value4


INLINE [
    Value, Sno
    66, 1
    55, 2
    44, 3
    44, 4
    ., 5
    -, 6
    +, 7
    33, 8
    2, 9
];

output:

HTH,

PFA,

Hirish

Re: Multiple Values for SET NullInterpret?

dselgo_eidex — Thu, 28 Jan 2016 22:07:34 GMT

Thanks for the help everyone. I ended up replacing the '.' and '-' values with NULLs but I was mostly wondering if it was possible to put more values into NullInterpret.