Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Master II
Master II

Missing values-Replace with string

Hi All,

I have three tables in the data model. Getting the NULL values in my report for Country Name.

I want to show this missing values along with 'AUSTRALIA' and 'JAPAN as TECHNO.

script:

Directory;

LOAD Cn_Key,

     Cn_Name as Cn_Name_Original,

if(WildMatch(Cn_Name,'AUSTRALIA','JAPAN','',' ','-') ,'TECHNO',Cn_Name) as Cn_Name

FROM

Null_Values_Replace.xls

(biff, embedded labels, table is Country$);

Directory;

LOAD Tr_Key,

     Tr_Name

FROM

Null_Values_Replace.xls

(biff, embedded labels, table is Region$);

Directory;

LOAD Cn_Key,

     Tr_Key,

     Cust_Key,

     Sales,

     Tr

FROM

Null_Values_Replace.xls

(biff, embedded labels, table is Customer$);

Capture.PNG

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Hi,

Try this script

MapCountry:

Mapping Load

Cn_Key,     

if(WildMatch(Cn_Name,'AUSTRALIA','JAPAN','',' ','-') ,'TECHNO',Cn_Name) as Cn_Name

FROM

Null_Values_Replace.xls

(biff, embedded labels, table is Country$);

Directory;

LOAD Tr_Key,

     Tr_Name

FROM

Null_Values_Replace.xls

(biff, embedded labels, table is Region$);

Directory;

LOAD Cn_Key,

  ApplyMap('MapCountry',  Cn_Key, 'TECHNO') AS Cn_Name,

     Tr_Key,

     Cust_Key,

     Sales,

     Tr

FROM

Null_Values_Replace.xls

(biff, embedded labels, table is Customer$);

Regards,

Jagan.

View solution in original post

9 Replies
MVP & Luminary
MVP & Luminary

Try If(match(Cn_Name, 'AUSTRALIA','JAPAN','-') or len(trim(Cn_Name))=0, 'TECHNO',Cn_Name) as Cn_Name

The Len(Trim(..)) construction will not only replace null values but also empty strings and values that contain only spaces.


talk is cheap, supply exceeds demand
Specialist III
Specialist III

if(WildMatch(Cn_Name,'AUSTRALIA','JAPAN','',' ','-') or isNull(Cn_Name) ,'TECHNO',Cn_Name) as Cn_Name

MVP & Luminary
MVP & Luminary

You can also use QlikView null-handling functionality, such as NullAsValue.

Check out my new book QlikView Your Business. I describe there in detail how to deal with NULL values (among many other developer techniques).

cheers,

Oleg Troyansky

QlikView Your Business: An expert guide to Business Discovery with QlikView and Qlik Sense

Master II
Master II

Hi gwassenaar

I applied your logic. But still it is showing  '-' values for Country.

Please help.

Untitled.png

MVP & Luminary
MVP & Luminary

Can't help you without the source data.


talk is cheap, supply exceeds demand
MVP & Luminary
MVP & Luminary

Hi Arjun,

Try Mapping Load in the third table and remove first table.  This will fix.

Regards,

Jagan.

MVP & Luminary
MVP & Luminary

Hi,

Try this script

MapCountry:

Mapping Load

Cn_Key,     

if(WildMatch(Cn_Name,'AUSTRALIA','JAPAN','',' ','-') ,'TECHNO',Cn_Name) as Cn_Name

FROM

Null_Values_Replace.xls

(biff, embedded labels, table is Country$);

Directory;

LOAD Tr_Key,

     Tr_Name

FROM

Null_Values_Replace.xls

(biff, embedded labels, table is Region$);

Directory;

LOAD Cn_Key,

  ApplyMap('MapCountry',  Cn_Key, 'TECHNO') AS Cn_Name,

     Tr_Key,

     Cust_Key,

     Sales,

     Tr

FROM

Null_Values_Replace.xls

(biff, embedded labels, table is Customer$);

Regards,

Jagan.

View solution in original post

Master II
Master II

Hi jagan moha,

Thank you so much for your out of box idea -mapping load.I added gwassenaar's advice as well in mapping table- len(trim(Cn_Name))=0 .

if(WildMatch(Cn_Name,'AUSTRALIA','JAPAN','',' ','-') or len(trim(Cn_Name))=0 ,'TECHNO',Cn_Name) as Cn_Name

Thank you all.

Partner
Partner

I am not able to reload your application so I implemented my logic in calculated dimension. I think, if you do the same in script, it will work.

qlik.png