Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
thymenkristen
New 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
New Contributor III

Re: How to replace missing values with a string value

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!

4 Replies
zhadrakas
Valued Contributor

Re: How to replace missing values with a string value

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
New Contributor III

Re: How to replace missing values with a string value

Thanks for your reply!

But still nothing is happening...

I get the same output..

zhadrakas
Valued Contributor

Re: How to replace missing values with a string value

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
New Contributor III

Re: How to replace missing values with a string value

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!