Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
NullInterpret is a variable, which can't hold an array of values. So what you are trying to do is not possible, IMHO.
NullInterpret is a variable, which can't hold an array of values. So what you are trying to do is not possible, IMHO.
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!
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
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.