Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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);
No Ramya. it is throwing error like below.
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);
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!
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;
Varun - thanks for response.
I am not able to replicate your code in my application. Can you please advice.
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);
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 |
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