How to replace missing values with a string value

[thymenkristen]

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)

I 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!

[zhadrakas]

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]

Thanks for your reply!

But still nothing is happening...

I get the same output..

[zhadrakas]

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 · Accepted solution]

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!