Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
thymenkristen
Contributor III
Contributor III

How to replace missing values with a string value

Hi Qlik community,

Currently I'm working on a project which requires to filter our customerbase. 

We linked specific labels to our customer key (Debiteur_Key)

Table.JPGI want to replace the '-' with 'None Specified' value. In order to filter exclusively between customer groups.

Currently my script looks like this:

Directory;
WFS_WTlabel:
LOAD Debiteur_Key,
BedrijfType as BedrijfsTypeKlant
FROM
[TradevsWF.xlsx]
(xxxx);

WTRADEFILTER:
LOAD
Debiteur_Key,
if(Len(Trim(BedrijfsTypeKlant))>0, BedrijfsTypeKlant, 'None Specified') as WTfilter

Resident WFS_WTlabel;

Drop Table WFS_WTlabel;

But this is not providing me the desired result...

Can someone help me? Thanks in advance!

1 Solution

Accepted Solutions
thymenkristen
Contributor III
Contributor III
Author

Thanks Tim, you made think..

 

And i found the solution;

My initial if(len( statement worked, but first i had to left_join the data and then reload the script.

Thanks for your help!

View solution in original post

4 Replies
zhadrakas
Specialist II
Specialist II

Try like this:

Directory;
WFS_WTlabel:
LOAD Debiteur_Key,
BedrijfType as BedrijfsTypeKlant
FROM
[TradevsWF.xlsx]
(xxxx);

NullAsValue *; SET NullValue = 'None Specified';

WTRADEFILTER:
LOAD
Debiteur_Key,
BedrijfsTypeKlant as WTfilter

Resident WFS_WTlabel;

Drop Table WFS_WTlabel;

 

Note:

This works for all following Fields:

NullAsValue *; SET NullValue = ‘<NULL>’

This will only effect your desired field:

NullAsValue BedrijfsTypeKlant;SET NullValue = ‘<NULL>

 

thymenkristen
Contributor III
Contributor III
Author

Thanks for your reply!

But still nothing is happening...

I get the same output..

zhadrakas
Specialist II
Specialist II

then i think this are not real NULL Values.

Please check the values in Excel.

Maybe this line are already in Excel like "-"

then you can use:

if(trim(FIELD)='-', 'None Specified', FIELD) as FIELD

regards

tim

thymenkristen
Contributor III
Contributor III
Author

Thanks Tim, you made think..

 

And i found the solution;

My initial if(len( statement worked, but first i had to left_join the data and then reload the script.

Thanks for your help!