ApplyMap Function

[Foodie123]

Hello Experts,

I am using the ApplyMap Function to change the fields but some of the fields are not changing correctly. This is mostly occurring with the fields that have spaces between them.

For one of fields, I am trying to change to DeKalb, but I am getting some De Kalb. 

Another field, I am trying to change to LaPorte, but I am getting some La Porte or Laporte.

I'm thinking this is because of trailing spaces.. but I'm not sure how to use the trim function within the applymap function.

Any help is appreciated!

[BrunPierre]

Hi, if you have a country code in tblHomeBook, map the country code to the country instead. That way you need not worry about fixing the inconsistencies.

Anyway, if it's just the spaces anywhere, then perhaps like this.

ApplyMap('Country', Trim(PurgeChar(Country,' ') ) as Country

[Chanty4u]

Hi you modify your each applymap condition

ApplyMap('MapName', Trim(FieldName), 'LaPorte')

[Foodie123]

Hmm, I tried that script and it looks like it didn't work, so the spaces are probably not the problem..

[BrunPierre]

Can you post some samples?

[Marijn]

Can you show us the results from your script?

It might be the spaces, did you make sure you also don't load any spaces in the map? Because then it won't match the values.

If you use applymap like this:
ApplyMap('County',County,Null())
You should get null values when the map is unable to make any match with a value.

[stevedark · Accepted solution]

Hi @Foodie123 

With the ApplyMap function if no match is found then the source value is used. This is most likely what is happening here.

You can prove that by adding a third parameter which adds not found in front of the result:

ApplyMap('County', County, 'Not Found: ' & County) as County,

I suspect you can do a more fuzzy match on the field you are matching, so that you get more positive matches. This will also reduce the number of rows you need to have in the replace spreadsheet.  You could, for instance just match on upper case alpha characters.

So the creation of the mapping table would look like this:

County:
MAPPING LOAD
    KeepChar(Upper(ChangeFrom), 'ABCDEFGHIJKLMNOPQRSTUVWXYZ') as CF,
    ChangeTo
FROM etc.

And where you use it would look like:

ApplyMap('County', KeepChar(Upper(County), 'ABCDEFGHIJKLMNOPQRSTUVWXYZ')) as County,

This should sort it out for you, unless you have some very weird characters in there!

I've done a blog post (old, but still relevant) on ApplyMap which you may find useful:

https://www.quickintelligence.co.uk/applymap-is-it-so-wrong/

Hope that helps,

Steve

[Foodie123]

Thank you Steve! This was very helpful!

Do you know what I could do about the unmatched fields if I don't want the source value to be used and instead have them change to the correct field that I want it to?

[stevedark]

Hi @Foodie123 

You can put whatever you like in the third parameter of the ApplyMap statement, for instance:

ApplyMap('County', KeepChar(Upper(County), 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'), 'Other') as County,

Or

ApplyMap('County', KeepChar(Upper(County), 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'), AnotherField) as County,

Or

ApplyMap('County', KeepChar(Upper(County), 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'), 'Not Matched: ' & County) as County,

Cheers,

Steve

[Foodie123]

Thank you for all your help!