Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikviewwizard
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
jagan
Luminary Alumni
Luminary Alumni

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


talk is cheap, supply exceeds demand
marcus_malinow
Specialist III
Specialist III

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
Master II
Master II
Author

Hi gwassenaar

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

Please help.

Untitled.png

Gysbert_Wassenaar

Can't help you without the source data.


talk is cheap, supply exceeds demand
jagan
Luminary Alumni
Luminary Alumni

Hi Arjun,

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

Regards,

Jagan.

jagan
Luminary Alumni
Luminary Alumni

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
Master II
Master II
Author

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
Partner - Specialist
Partner - Specialist

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