Solved: NullASValue not working, sql load - Qlik Community

Pico · ‎2022-03-21

Hi all,

I have issues using the command NullASValue.

Right now I have two loading from SQL scripts, like follows:

---------------------------------------------Section 1

Tab1:

NullAsValue *;
set NullValue = 'NULL';
SQL

select a.ID

from my_Anag a;

-------------------------------------------- Section 2

Tab2:

NullAsValue *;
set NullValue = 'NULL';

SQL

select b.ID, b.number

from My_Numbers b;

-----------------------------------------------

At this point Qlik joins the two tables, where the first one has much more IDs than the second one (containing also another filed, 'number', associated with the IDs).

the result is similar to:

ID	Number
1	12345
2	-
3	45674
4	-

Instead I would like to have something that looks like:

ID	Number
1	12345
2	n.a.
3	45674
4	n.a.

Thank you everyone for your time

durgesh22 · ‎2022-03-21

Hi Pico,

try below, Hope it will work for you.

it is not working unless we join both tables.

if you are referring only one column they try Apply_Map()

---------------------------------------------Section 1

Tab1:
SQL
select a.ID
from my_Anag a;

-------------------------------------------- Section 2
left Join
Tab2:
SQL
select b.ID, b.number
from My_Numbers b;

-----------------------------------------------
Table

ID "=if(IsNull(Number),'n.a.',Number)"
1 12345
2 n.a.
3 45674
4 n.a.

View solution in original post

marcus_sommer · ‎2022-03-21

Your approach isn't a join else you associate two tables - from an UI point of view. This means there is no NULL on the script-side which could be replaced with a default-value - your NULL happens later when the UI build the views. Depending on your requirements you could try to fetch it there within charts maybe with something like:

alt(Number, 'n.a.')

But it won't work within table-boxes and you could select/access it directly. If this is needed you must merge both tables within the script, maybe with:

m: mapping load *;
SQL select b.ID, b.number
from My_Numbers b;

t: load *, applympa('m', a.ID, 'n.a.') as number;
SQL select a.ID
from my_Anag a;

- Marcus

View solution in original post

Chanty4u · ‎2022-03-21

Hi

you can try this condition at end of the table as where

WHERE 1=1;

Pico · ‎2022-03-21

Sorry, I forgot to say that I've already tried it.

Chanty4u · ‎2022-03-21

Try adding NullInterpret function above null value function, you may have blank which are not converting into 0.

SET NullInterpret ='';

NullAsValue *;
set NullValue = 'NULL';

Pico · ‎2022-03-21

Sadly it doesn't work

Anil_Babu_Samineni · ‎2022-03-21

Hello, there are many possibility that can deserve this, If your nulls are real nulls you can either try

If(IsNull(Number), 'NA', Number) as Number

Or

If(Len(Number)=0, 'NA', Number) as Number

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

Pico · ‎2022-03-21

It doesn't work.
Btw there are no null values in the DB I am loading from SQL: the null values comes from the join that Qlik sense applies to my two tables, if I understood correctly how it works.

Anil_Babu_Samineni · ‎2022-03-21

It's good, If you can share the sample data set to check the same?

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

durgesh22 · ‎2022-03-21

Hi Pico,

try below, Hope it will work for you.

it is not working unless we join both tables.

if you are referring only one column they try Apply_Map()

---------------------------------------------Section 1

Tab1:
SQL
select a.ID
from my_Anag a;

-------------------------------------------- Section 2
left Join
Tab2:
SQL
select b.ID, b.number
from My_Numbers b;

-----------------------------------------------
Table

ID "=if(IsNull(Number),'n.a.',Number)"
1 12345
2 n.a.
3 45674
4 n.a.

Chanty4u · ‎2022-03-21

try this

=if(Number='','N/A',Number)

or

=if(Number='-','N/A',Number)

NullASValue not working, sql load

Data Load Editor