Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

How to interpret a value as Null from Database?

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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

You could do something like

NULLMAP:

MAPPING

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

AUTOGENERATE 1;

MAP * USING NULLMAP;

YourTable:

SELECT FIELD1, FIELD2 FROM ....;

View solution in original post

14 Replies
Anonymous
Not applicable
Author

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

NullAsValue *;

Set NullValue = 'NULL';

sql ......

Anonymous
Not applicable
Author

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
Not applicable
Author

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

swuehl
MVP
MVP

You could do something like

NULLMAP:

MAPPING

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

AUTOGENERATE 1;

MAP * USING NULLMAP;

YourTable:

SELECT FIELD1, FIELD2 FROM ....;

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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:

 

KeyValue
+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
MVP
MVP

Have you tried a mapping table like

NULLMAP:

MAPPING

LOAD Key, NULL() as Value

INLINE [

Key

+

>

<

];

Anonymous
Not applicable
Author

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
MVP
MVP

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.