Missing values-Replace with string

[qlikviewwizard]

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$);

[Gysbert_Wassenaar]

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.

[marcus_malinow]

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

[Oleg_Troyansky]

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

[qlikviewwizard]

Hi gwassenaar

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

Please help.

[Gysbert_Wassenaar]

Can't help you without the source data.

[jagan]

Hi Arjun,

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

Regards,

Jagan.

[jagan · Accepted solution]

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.

[qlikviewwizard]

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.

[jsingh71]

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.