Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
Hope everyone is fine.
We want to use apply map on country code field from a table.
The Country Code table is as follows:
1 USA
7 Russia
20 Egypt
92 Pakistan
919 India
etc. etc.
The field we have from the table which we want to apply map on is as follows:
1212
92308
91977
We want to apply map and match on the first few numbers and then display the country.
For example if we have the number 92308 then it should display Pakistan as 92 matches with the 92 in the country code.
How is it possible we can do the apply map and search for the starting few digits to match the country code.
Will appreciate if someone can assist us in this.
Thanks.
Try this:
Mapping:
Mapping LOAD * Inline [
Val, Country
1, USA
7, Russia
20, Egypt
92, Pakistan
919, India
];
Table:
LOAD Val,
ApplyMap('Mapping', Left(Val, 3), ApplyMap('Mapping', Left(Val, 2), ApplyMap('Mapping', Left(Val, 1)))) as MappedVal
Inline [
Val
1212
92308
91977
];
Output in a table box:
Try this:
Mapping:
Mapping LOAD * Inline [
Val, Country
1, USA
7, Russia
20, Egypt
92, Pakistan
919, India
];
Table:
LOAD Val,
ApplyMap('Mapping', Left(Val, 3), ApplyMap('Mapping', Left(Val, 2), ApplyMap('Mapping', Left(Val, 1)))) as MappedVal
Inline [
Val
1212
92308
91977
];
Output in a table box:
Since the codes you want to lookup vary in length, you'ld better use MapSubString() as demonstrated here: Re: Find Substring, and link to another field
Peter
Haven't tested but may be you can use like below
Country:
Load * Inline
[
CODE, COUNTRY
1, USA
7, Russia
20, Egypt
92, Pakistan
919, India
];
Join
Load * Inline
[
Field
1212
92308
91977
];
NoConcatenate
Final:
Load * Resident Country
Where Index(Field,CODE)>0;
Drop Table Country;
If you create a mapping table of code and country, then you could use the 'unmatched' parameter of ApplyMap like this:
mapCountries:
Mapping Load Code, Country From .....
;
LOAD
PhoneNo,
ApplyMap('mapCountries', Left(PhoneNo, 1),
ApplyMap('mapCountries', Left(PhoneNo, 2),
ApplyMap('mapCountries', Left(PhoneNo, 3),
ApplyMap('mapCountries', Left(PhoneNo, 4), 'Unknown'
)
)
)
) As Country,
This will test for 1 character codes, then 2 character codes. If the code is not found at all, then return 'Unknown'
jonathan dienst I have a similar script like yours, the only difference is that I am testing it 3,2,1. I feel if we do it 1,2,3,4 then if 9 is abc and 92 is def. and if we want to know something like 92345, wouldn't it give abc here, whereas it should give def??? Let me know what you think?
Thanks for looking into this
Best,
Sunny
Sunny
I think these are telephone country codes, so that scenario does not occur, but you could certainly test them that way round.
Did not think that way . Thanks jonathan
Sunindia,
Thank you very much.... worked like a charm.
Awesome
I am glad I was able to help.
Best,
Sunny