Multiple Values for SET NullInterpret?

[dselgo_eidex]

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.

[swuehl · Accepted solution]

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

[Oleg_Troyansky]

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!

[HirisH_V7]

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

[dselgo_eidex]

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.