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.
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:
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
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:
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?
