Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
karthick01
New Contributor III

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

13 Replies
Highlighted
marcioarruda
Contributor III

Re: How to interpret a value as Null from Database?

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

NullAsValue *;

Set NullValue = 'NULL';

sql ......

Highlighted
karthick01
New Contributor III

Re: How to interpret a value as Null from Database?

Hi Marcio,

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

Highlighted
marcioarruda
Contributor III

Re: How to interpret a value as Null from Database?

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

Highlighted
MVP
MVP

Re: How to interpret a value as Null from Database?

You could do something like

NULLMAP:

MAPPING

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

AUTOGENERATE 1;

MAP * USING NULLMAP;

YourTable:

SELECT FIELD1, FIELD2 FROM ....;

Highlighted
karthick01
New Contributor III

Re: How to interpret a value as Null from Database?

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

Highlighted
karthick01
New Contributor III

Re: How to interpret a value as Null from Database?

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?

Highlighted
MVP
MVP

Re: How to interpret a value as Null from Database?

Have you tried a mapping table like

NULLMAP:

MAPPING

LOAD Key, NULL() as Value

INLINE [

Key

+

>

<

];

Highlighted
karthick01
New Contributor III

Re: How to interpret a value as Null from Database?

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?

Highlighted
MVP
MVP

Re: How to interpret a value as Null from Database?

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.