Replace nulls

[PrashantRupani]

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

[QFabian · Accepted solution]

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;

[ArnadoSandoval · Accepted solution]

Hi @PrashantRupani 

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
];

[PrashantRupani]

Thank you Arnado. This is extremely helpful to know. 

[Unoriginal]

Never works