Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
Hi
you can try this condition at end of the table as where
WHERE 1=1;
Sorry, I forgot to say that I've already tried it.
Try adding NullInterpret function above null value function, you may have blank which are not converting into 0.
SET NullInterpret ='';
NullAsValue *;
set NullValue = 'NULL';
Sadly it doesn't work
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
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.
It's good, If you can share the sample data set to check the same?
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.
try this
=if(Number='','N/A',Number)
or
=if(Number='-','N/A',Number)