topic How to replace missing values with a string value in QlikView

How to replace missing values with a string value

thymenkristen — Wed, 25 Nov 2020 16:16:04 GMT

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 — Thu, 02 May 2019 08:13:05 GMT

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 — Thu, 02 May 2019 08:30:06 GMT

Thanks for your reply!

But still nothing is happening...

I get the same output..

zhadrakas — Thu, 02 May 2019 08:35:40 GMT

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 — Thu, 02 May 2019 08:53:00 GMT

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!