How to interpret a value as Null from Database?

[Anonymous]

Hi,

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?

Thanks

[Anonymous]

If you want to treat null values such as "NULL", you can use the following:

NullAsValue *;

Set NullValue = 'NULL';

sql ......

[Anonymous]

Hi Marcio,

Nullasvalue work only for real Nullvalue. But my requirement is to interpret any value say '+' as Null. How do we do that?

[Anonymous]

I'm sorry, but I do not understand ... Are there any fields in your database that have junk content? Such as the '+' character?

[swuehl · Accepted solution]

You could do something like

NULLMAP:

MAPPING

LOAD '+' as Key, NULL() as Value

AUTOGENERATE 1;

MAP * USING NULLMAP;

YourTable:

SELECT FIELD1, FIELD2 FROM ....;

[Anonymous]

Yes, there are some fields like '+','<' etc which should be treated as Null.

[Anonymous]

Hi Stefan,

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.

Example:

Key	Value
+	Null()
>	Null()

.

.

.

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?

[swuehl]

Have you tried a mapping table like

NULLMAP:

MAPPING

LOAD Key, NULL() as Value

INLINE [

Key

+

>

<

];

[Anonymous]

Stefan,

The one you provided worked perfectly. Thank you so much Stefan.

FYI, I tried the following logic and that also has worked:

LOAD Year,

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?

[swuehl]

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.

LOAD Year,

      Num(Amount) as Amount,

     ...

Should return similar results.