Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
sjhussain
Partner - Creator II
Partner - Creator II

Apply Map on Substring

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.

1 Solution

Accepted Solutions
sunny_talwar

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:


Capture.PNG


View solution in original post

9 Replies
sunny_talwar

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:


Capture.PNG


Peter_Cammaert
Partner - Champion III
Partner - Champion III

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
MVP
MVP

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
Partner - Champion III
Partner - Champion III

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'

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
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
Partner - Champion III
Partner - Champion III

Sunny

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
sunny_talwar

Did not think that way . Thanks jonathan

sjhussain
Partner - Creator II
Partner - Creator II
Author

Sunindia,

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

sunny_talwar

Awesome

I am glad I was able to help.

Best,

Sunny