topic Re: Missing values-Replace with string in QlikView

Missing values-Replace with string

qlikviewwizard — Thu, 22 Oct 2015 14:17:51 GMT

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,

FROM

Null_Values_Replace.xls

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

Re: Missing values-Replace with string

Gysbert_Wassenaar — Thu, 22 Oct 2015 14:21:04 GMT

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.

Re: Missing values-Replace with string

marcus_malinow — Thu, 22 Oct 2015 14:21:13 GMT

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

Re: Missing values-Replace with string

Oleg_Troyansky — Thu, 22 Oct 2015 14:40:13 GMT

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

Re: Missing values-Replace with string

qlikviewwizard — Thu, 22 Oct 2015 15:25:25 GMT

Hi gwassenaar

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

Please help.

Re: Missing values-Replace with string

Gysbert_Wassenaar — Thu, 22 Oct 2015 15:43:36 GMT

Can't help you without the source data.

Re: Missing values-Replace with string

jagan — Fri, 23 Oct 2015 07:24:46 GMT

Hi Arjun,

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

Regards,

Jagan.

Re: Missing values-Replace with string

jagan — Fri, 23 Oct 2015 07:26:07 GMT

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,

FROM

Null_Values_Replace.xls

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

Regards,

Jagan.

Re: Missing values-Replace with string

qlikviewwizard — Fri, 23 Oct 2015 08:42:17 GMT

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.

Re: Missing values-Replace with string

jsingh71 — Fri, 23 Oct 2015 08:43:59 GMT

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.