
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Replace nulls
Hi,
I am joining Table A with Table B in the load script
Table A ( Col 1, Col 2)
Table B ( Col1, Col 3)
For example - the data in the table looks like below.
Table A
Col 1 , Col 2
101, John
102, Joe
Table B
Col 1 , Col 3
101,
102, US
Not all Col 1 in Table A will have a value in Col 3. 101 does not have any value in Col 3.
I have put then put a table ( Col 1, Col 2, Col3 ) in the sheet after loading the data and I would like to replace missing values with a text 'Data Missing'. I tried using =if(isnull(Col 1),'Data_Missing',Col 1). But it does not yield the result I want.
Please advise where I am going wrong
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
i @PrashantRupani , you can use your expression to make a new table resulting from your A table that contains 4 fields.
your script returns one tabe called A with the four fields.
then
A2:
Load
if(isnull(Col 1),'Data_Missing',Col 1) as Column1,
if(isnull(Col 2),'Data_Missing',Col 2) as Column2,
if(isnull(Col 3),'Data_Missing',Col 3) as Column3
Resident A;
drop table A;

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You may work with @QFabian solution or implement Qlik's script features to handle NULLs, these features are:
- NullInterpret : set NullInterpret =''; // '' are interpreted as NULL
- NullValue : Set NullValue='Data_Missing'; // Null values will be replaced with the text 'Data_Missing'
- NullAsValue : NullAsValue [Col 1], [Col 2], [Col 3]; // the fields to apply the NullInterpret and NullValue
The script code, based in your example:
These are build-in Qlik features to handle null, I attached a demo application QVF; the demo implement the Null logic for two different data sets.
set NullInterpret =''; // '' are interpreted as NULL
Set NullValue='Data_Missing'; // Null values will be replaced with the text 'Data_Missing'
NullAsValue [Col 1], [Col 2], [Col 3]; // the fields to apply the NullInterpret and NullValue
Table_A:
Load * Inline [
Col 1 , Col 2
101, John
102, Joe
103,
,Peter
];
Left Join(Table_A)
Table_B:
Load * Inline [
Col 1 , Col 3
101,
102, US
103, AUS
, MX
];
A journey of a thousand miles begins with a single step.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
i @PrashantRupani , you can use your expression to make a new table resulting from your A table that contains 4 fields.
your script returns one tabe called A with the four fields.
then
A2:
Load
if(isnull(Col 1),'Data_Missing',Col 1) as Column1,
if(isnull(Col 2),'Data_Missing',Col 2) as Column2,
if(isnull(Col 3),'Data_Missing',Col 3) as Column3
Resident A;
drop table A;

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You may work with @QFabian solution or implement Qlik's script features to handle NULLs, these features are:
- NullInterpret : set NullInterpret =''; // '' are interpreted as NULL
- NullValue : Set NullValue='Data_Missing'; // Null values will be replaced with the text 'Data_Missing'
- NullAsValue : NullAsValue [Col 1], [Col 2], [Col 3]; // the fields to apply the NullInterpret and NullValue
The script code, based in your example:
These are build-in Qlik features to handle null, I attached a demo application QVF; the demo implement the Null logic for two different data sets.
set NullInterpret =''; // '' are interpreted as NULL
Set NullValue='Data_Missing'; // Null values will be replaced with the text 'Data_Missing'
NullAsValue [Col 1], [Col 2], [Col 3]; // the fields to apply the NullInterpret and NullValue
Table_A:
Load * Inline [
Col 1 , Col 2
101, John
102, Joe
103,
,Peter
];
Left Join(Table_A)
Table_B:
Load * Inline [
Col 1 , Col 3
101,
102, US
103, AUS
, MX
];
A journey of a thousand miles begins with a single step.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you Arnado. This is extremely helpful to know.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Never works
