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.
