Help to value to country code?

[durgabhavani]

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. 

[ramyasaiqv]

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

[durgabhavani]

No Ramya. it is throwing error like below.

[ramyasaiqv]

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

[madhumitha]

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!

[techvarun]

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;

[durgabhavani]

Varun - thanks for response.

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

[shraddha_g]

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

[durgabhavani]

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

[techvarun]

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