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

How to interpret a value as Null from Database?

Anonymous — Wed, 09 Nov 2016 18:20:54 GMT

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

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

Anonymous — Wed, 09 Nov 2016 18:41:58 GMT

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

NullAsValue *;

Set NullValue = 'NULL';

sql ......

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

Anonymous — Wed, 09 Nov 2016 19:25:33 GMT

Hi Marcio,

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

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

Anonymous — Wed, 09 Nov 2016 19:46:47 GMT

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

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

swuehl — Wed, 09 Nov 2016 20:04:02 GMT

You could do something like

NULLMAP:

MAPPING

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

AUTOGENERATE 1;

MAP * USING NULLMAP;

YourTable:

SELECT FIELD1, FIELD2 FROM ....;

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

Anonymous — Wed, 09 Nov 2016 20:05:23 GMT

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

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

Anonymous — Wed, 09 Nov 2016 21:17:29 GMT

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?

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

swuehl — Wed, 09 Nov 2016 21:22:02 GMT

Have you tried a mapping table like

NULLMAP:

MAPPING

LOAD Key, NULL() as Value

INLINE [

Key

];

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

Anonymous — Wed, 09 Nov 2016 21:37:57 GMT

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?

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

swuehl — Wed, 09 Nov 2016 21:53:52 GMT

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.

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

Anonymous — Wed, 09 Nov 2016 22:18:57 GMT

Thank you so much Stefan

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

johanlindell — Thu, 10 Nov 2016 10:19:28 GMT

There is a function to check if a value is a number, IsNum. You can use that:

LOAD Year,

If (IsNum(Amount), Amount, Null ()) as Amount,

xxx

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

Anonymous — Thu, 10 Nov 2016 17:39:21 GMT

Yes, this logic also works. Thanks Johan

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

Anonymous — Thu, 10 Nov 2016 20:41:27 GMT

Try this Karthick Sudalaiyandi:

On load the script do

If(FindOneOf(Amount,'+-><') > 0 , 'NULL',Amount) AS Amount

In the red part put the possible characters that will be considered null.

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

Digvijay_Singh — Thu, 14 Apr 2022 22:57:04 GMT

Sweet!!! Thanks as always!!!!