I am trying to add latitude and longitude to a number of accounts from our CRM system. I have taken a look on all of the QlikView-examples i can find regarding this and come up with a script that reads the account table into QlikView, concatenates a string that i send to Google with the following request.
Adress_tmp:
LOAD
'$(vAccountId)'
AS Id,
'$(vAccountName)'
AS Name,
'$(vTimeStamp)'
AS CreateDate,
'INITIAL LOAD'
AS CreateInfo,
[Response/name]
AS CityName,
[Response/Placemark/id]
AS RespID,
[Response/Placemark/address]
AS RespAddress,
subfield
([Response/Placemark/Point/coordinates],',',2) & ',' & subfield([Response/Placemark/Point/coordinates],',',1) AS coordinates,
subfield
([Response/Placemark/Point/coordinates],',',2) AS latitude,
subfield
([Response/Placemark/Point/coordinates],',',1) AS longitude
FROM
[http://maps.google.com/maps/geo?q=$(vAdress)&output=xml&oe=utf8&sensor=false&key=ABQIAAAAikInoFhtkuTuBnStG028IhRppRQhfVfLjr801Yij-qL5McAFvhTsGS0FzLbbW5CYxc_ItcR7DM-9xw] (XmlSimple, Table is [kml]); [http://maps.google.com/maps/geo?q=$(vAdress)&output=xml&oe=utf8&sensor=false&key=ABQIAAAAikInoFhtkuTuBnStG028IhRppRQhfVfLjr801Yij-qL5McAFvhTsGS0FzLbbW5CYxc_ItcR7DM-9xw] (XmlSimple, Table is [kml]);
[http://maps.google.com/maps/geo?q=$(vAdress)&output=xml&oe=utf8&sensor=false&key=ABQIAAAAikInoFhtkuTuBnStG028IhRppRQhfVfLjr801Yij-qL5McAFvhTsGS0FzLbbW5CYxc_ItcR7DM-9xw] (XmlSimple, Table is [kml]);
vAdress is the concatenated string of different adressfields looking something like this:
'Bodalsvägen 2-681 43-Kristinehamn--Sweden'
I have also noticed in all the examples i can find that some kind of replacement via replace or a mapping load is done on special characters like å --> a, é -->, Ö --> O
The reason for doing this is that if a string containing a special character is sent to Google and Google can't resolve it, the logic that all of the examples i looked at contained, and the one i am using, freezes. Pretty much the logic is a loop over the amount of rows in the account table doing the above request loading a row into Adress_tmp and then taking the next row from the account table and sending the request.
I have also tried doing this, replacing the special characters, and it works to some degree but i simply can't do this on all accounts on a global scale with all the country specific characters that excist.
Has anybody run into the above problem and have a working solution?
My guess is that i have to come up with some logic that can catch the return from Google when a strange character is sent, I think Google returns something saying nothing found or an empty string, log the row that didn't return any answer and then move on to the next row in the account table sending a new string. Some kind of exception handling preventing my script from freezing and not finishing.
