Apply Map on Substring

[sjhussain]

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.

[sunny_talwar · Accepted solution]

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:

[Peter_Cammaert]

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

[MK_QSL]

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;

[jonathandienst]

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'

[sunny_talwar]

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

[jonathandienst]

Sunny

I think these are telephone country codes, so that scenario does not occur, but you could certainly test them that way round.

[sunny_talwar]

Did not think that way . Thanks jonathan‌

[sjhussain]

Sunindia,

Thank you very much.... worked like a charm.

[sunny_talwar]

Awesome

I am glad I was able to help.

Best,

Sunny