Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Arjunarao
Honored Contributor 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

Re: Missing values-Replace with string

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.

9 Replies
MVP & Luminary
MVP & Luminary

Re: Missing values-Replace with string

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
marcus_malinow
Valued Contributor III

Re: Missing values-Replace with string

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

MVP & Luminary
MVP & Luminary

Re: Missing values-Replace with string

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

Arjunarao
Honored Contributor II

Re: Missing values-Replace with string

Hi gwassenaar

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

Please help.

Untitled.png

MVP & Luminary
MVP & Luminary

Re: Missing values-Replace with string

Can't help you without the source data.


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

Re: Missing values-Replace with string

Hi Arjun,

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

Regards,

Jagan.

MVP & Luminary
MVP & Luminary

Re: Missing values-Replace with string

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.

Arjunarao
Honored Contributor II

Re: Missing values-Replace with string

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

Re: Missing values-Replace with string

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