Discussion Board for collaboration related to QlikView App Development.
Using NullInterpret, we can interpret any value as a null value. Example, '+' can be interpreted as a null value using the NullInterpret variable. But by definition of Nullinterpret,
"The defined symbol will when it occurs in a text file or an inline statement be interpreted as NULL."
So, if I want to interpret something ('+') from the database, how can we do that? is there any variable similar to Nullinterpret or we need to handle that logically using If statement?
Nullasvalue work only for real Nullvalue. But my requirement is to interpret any value say '+' as Null. How do we do that?
You could do something like
LOAD '+' as Key, NULL() as Value
MAP * USING NULLMAP;
SELECT FIELD1, FIELD2 FROM ....;
Thanks. This works for one value such as '+'. But i do have other values '<','>' etc. How to handle this?
I tried creating a mapping spreadsheet with all the possible values and mapped all those values to Null. And then used that in the script. But the issue is Null() is taken as text.
This is the mapping table. And when i use Map using Qlikview is taking Null() as text instead of real null. How to tackle this scenario?
The one you provided worked perfectly. Thank you so much Stefan.
FYI, I tried the following logic and that also has worked:
alt(Num(Amount),Null()) as Amt......
Here the Amount field has the special characters such as '<','+' etc.
Does this logic make sense to you? or is there any hidden issue with this?
You don't need the Num() function, it just formats a number, but will not change the underlying numeric representation (unless you explicitely want to format).
Your approach should work, if you are coping with fields that hold basically only numbers, and any other text value should be replaced with NULL.
Num(Amount) as Amount,
Should return similar results.