topic Re: Apply Map on Substring in QlikView

Apply Map on Substring

sjhussain — Mon, 13 Jul 2015 14:17:12 GMT

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.

Re: Apply Map on Substring

sunny_talwar — Mon, 13 Jul 2015 14:38:56 GMT

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:

Re: Apply Map on Substring

Peter_Cammaert — Mon, 13 Jul 2015 14:45:06 GMT

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

Re: Apply Map on Substring

MK_QSL — Mon, 13 Jul 2015 14:46:44 GMT

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;

Re: Apply Map on Substring

jonathandienst — Mon, 13 Jul 2015 14:50:53 GMT

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'

Re: Apply Map on Substring

sunny_talwar — Mon, 13 Jul 2015 14:55:30 GMT

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

Re: Apply Map on Substring

jonathandienst — Mon, 13 Jul 2015 15:15:06 GMT

Sunny

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

Re: Apply Map on Substring

sunny_talwar — Mon, 13 Jul 2015 15:16:31 GMT

Did not think that way . Thanks jonathan‌

Re: Apply Map on Substring

sjhussain — Wed, 15 Jul 2015 14:49:27 GMT

Sunindia,

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

Re: Apply Map on Substring

sunny_talwar — Wed, 15 Jul 2015 14:50:53 GMT

Awesome

I am glad I was able to help.

Best,

Sunny