Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Contributor III
Contributor III

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
Highlighted
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>

 

Highlighted
Contributor III
Contributor III

Thanks for your reply!

But still nothing is happening...

I get the same output..

Highlighted
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

Highlighted
Contributor III
Contributor III

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