topic Re: Help to value to country code? in QlikView

Help to value to country code?

durgabhavani — Wed, 08 Nov 2017 04:24:35 GMT

Hi All,

I have a mapping file with below country codes.

Ctry_Cde	Country
AU	Austrialia
IN	India
BM	Bermuda
CN	China
BAN	Bangladesh
NZ	New Zealand
AM	Armenia
PAK	Pakistan
TU	Turkey
BL	Belgium
HK	Hong Kong
US	United States
UK	United Kingdom

I have another field Txt country like below.

Text_Ctry

AU,IN,BM

CN,BAN,BL

PAK,NZ

AM,TU,UK

US,IN,XX

UK,HK,YY

HN,UK,IN

US,RM,CN

I want to assign the text value 'No Mapping' to country codes which are not in my mapping file (that means some new country codes will come in future and they needs to be assign text value "No Mapping". Here example XX,YY,HN,RM have no country codes in country mapping file). Please find the expected output below.

I am able to map the country codes to country names with below code, but not able to assign the text value No Mapping.

Mapping
CTY_TEMP:
LOAD Ctry_Cde,
Country
FROM
[Test Data.xlsx]
(ooxml, embedded labels, table is Sheet1);

LOAD Text_Ctry,
MapSubString('CTY_TEMP',Text_Ctry) AS Text_Ctry1
FROM
[Test Data.xlsx]
(ooxml, embedded labels, table is Sheet2);

Current Output with above code:

Text_Ctry_Final

Austrialia,India,Bermuda

China,Bangladesh,Belgium

Pakistan,New Zealand

Armenia,Turkey,United Kingdom

United States,India,XX

United Kingdom,Hong Kong,YY

HN,United Kingdom,India

United States,RM,China

Expected Output

Text_Ctry_Final

Austrialia,India,Bermuda

China,Bangladesh,Belgium

Pakistan,New Zealand

Armenia,Turkey,United Kingdom

United States,India,No Mapping

United Kingdom,Hong Kong,No Mapping

No Mapping,United Kingdom,India

United States,No Mapping,China

Please advice.

Re: Help to value to country code?

ramyasaiqv — Wed, 08 Nov 2017 04:36:25 GMT

Try this.

Mapping
CTY_TEMP:
LOAD Ctry_Cde,
Country
FROM
[Test Data.xlsx]
(ooxml, embedded labels, table is Sheet1);

LOAD Text_Ctry,
MapSubString('CTY_TEMP',Text_Ctry, ‘No Mapping’) AS Text_Ctry1
FROM
[Test Data.xlsx]
(ooxml, embedded labels, table is Sheet2);

Re: Help to value to country code?

durgabhavani — Wed, 08 Nov 2017 04:47:14 GMT

No Ramya. it is throwing error like below.

Re: Help to value to country code?

ramyasaiqv — Wed, 08 Nov 2017 05:13:00 GMT

This should work. If not please upload the qvw with sample data.

Mapping
CTY_TEMP:
LOAD Ctry_Cde,
Country
FROM
[Test Data.xlsx]
(ooxml, embedded labels, table is Sheet1);

LOAD Text_Ctry,
MapSubString('CTY_TEMP',Text_Ctry, 'No Mapping') AS Text_Ctry1
FROM
[Test Data.xlsx]
(ooxml, embedded labels, table is Sheet2);

Re: Help to value to country code?

madhumitha — Wed, 08 Nov 2017 05:24:31 GMT

Hello Durga,

You cannot assign a default value using MapSubstring.

Its only possible with applymap.

Can you try adding country as 'No Mapping' for the unavailable codes in your source excel?

or you can do it in you script as concatenate load.

Thanks!

Re: Help to value to country code?

techvarun — Wed, 08 Nov 2017 05:41:21 GMT

CTY_TEMP:

mapping LOAD * INLINE [

Ctry_Cde, Country

AU, Austrialia

IN, India

BM, Bermuda

CN, China

BAN, Bangladesh

NZ, New Zealand

AM, Armenia

PAK, Pakistan

TU, Turkey

BL, Belgium

HK, Hong Kong

US, United States

UK, United Kingdom

];

CTY:

LOAD *, SubField(Text_Ctry,',') as Ctry_Cde INLINE [

Text_Ctry

"AU,IN,BM"

"CN,BAN,BL"

"PAK,NZ"

"AM,TU,UK"

"US,IN,XX"

"UK,HK,YY"

"HN,UK,IN"

"US,RM,CN"

];

CTY_TEMP1:

Mapping Load Ctry_Cde, ApplyMap('CTY_TEMP', Ctry_Cde, 'No Mapping') as Country Resident CTY;

LOAD Text_Ctry,

MapSubString('CTY_TEMP1',Text_Ctry) AS Text_Ctry1

Resident CTY;

Re: Help to value to country code?

durgabhavani — Wed, 08 Nov 2017 10:28:25 GMT

Varun - thanks for response.

I am not able to replicate your code in my application. Can you please advice.

Re: Help to value to country code?

shraddha_g — Wed, 08 Nov 2017 10:34:06 GMT

Mapping
CTY_TEMP:
LOAD Ctry_Cde,
Country
FROM
[Test Data.xlsx]
(ooxml, embedded labels, table is Sheet1);

LOAD Text_Ctry,
Applymap('CTY_TEMP',Text_Ctry,'No Mapping') AS Text_Ctry1
FROM
[Test Data.xlsx]
(ooxml, embedded labels, table is Sheet2);

Re: Help to value to country code?

durgabhavani — Wed, 08 Nov 2017 10:37:11 GMT

Not working shradda. it is giving below.

I am expecting below.

Text_Ctry	Text_Ctry_Final
AU,IN,BM	Austrialia,India,Bermuda
CN,BAN,BL	China,Bangladesh,Belgium
PAK,NZ	Pakistan,New Zealand
AM,TU,UK	Armenia,Turkey,United Kingdom
US,IN,XX	United States,India,No Mapping
UK,HK,YY	United Kingdom,Hong Kong,No Mapping
HN,UK,IN	No Mapping,United Kingdom,India
US,RM,CN	United States,No Mapping,China

Re: Help to value to country code?

techvarun — Thu, 09 Nov 2017 04:56:02 GMT

Hi,

What are the challenges?

Check the attached app and replace the INLINE table with your actual tables the app will work.

HTH

Thanks,

Varun

Re: Help to value to country code?

durgabhavani — Fri, 10 Nov 2017 04:40:57 GMT

I am able to resolve it with your code. thank you very much...

Re: Help to value to country code?

durgabhavani — Fri, 10 Nov 2017 04:43:12 GMT

I mark it as helpful. Correct answer is not populating.