Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All
I am trying to fetch Geo coordinates to my customer’s addresses, but can’t get it to work correctly
I have been searching community for answers on how to do it, and from what I have found I have put this script together, but I don’t get coordinates for all customers.
I only get one set of coordinates, that are placed on all customers.
Hope someone can help
Script to get coordinates:
let noRows = NoOfRows('Customers')-1;
for i=0 to $(noRows)
LET C = peek('vAddress', '$(i)', 'Customers');
if '$(C)' <> ', ' then
Data:
LOAD
'$(C)' as Address1,
[result/geometry/location/lat] as latitude,
[result/geometry/location/lng] as longitude,
[result/place_id]
FROM [http://maps.googleapis.com/maps/api/geocode/xml?address=$(address)&sensor=false] (XmlSimple, Table is [GeocodeResponse]);
end if
NEXT;
Regards
Niels Juel
Hi,
Have you checked your Address values is assigned to your Variable 'C'?
And i see that, you didn't give your variable in the website address..
FROM [http://maps.googleapis.com/maps/api/geocode/xml?address=$(address)$(C) &sensor=false] (XmlSimple, Table is [GeocodeResponse]);
And i assume, you want to exclude the address, if there is comma with space
if '$(C)' <> ', ' then Try this
if not WildMatch('$(b)','*, *') then
Complete script
let noRows = NoOfRows('Customers')-1;
for i=0 to $(noRows)
LET C = peek('vAddress', '$(i)', 'Customers');
if not WildMatch('$(b)','*, *') then
Data:
LOAD
'$(C)' as Address1,
[result/geometry/location/lat] as latitude,
[result/geometry/location/lng] as longitude,
[result/place_id]
FROM [http://maps.googleapis.com/maps/api/geocode/xml?address=$(C) &sensor=false] (XmlSimple, Table is [GeocodeResponse]);
end if
NEXT i;
Check it here too, Steps for beginners to implement Google Maps in Qlikview
Hi,
Have you checked your Address values is assigned to your Variable 'C'?
And i see that, you didn't give your variable in the website address..
FROM [http://maps.googleapis.com/maps/api/geocode/xml?address=$(address)$(C) &sensor=false] (XmlSimple, Table is [GeocodeResponse]);
And i assume, you want to exclude the address, if there is comma with space
if '$(C)' <> ', ' then Try this
if not WildMatch('$(b)','*, *') then
Complete script
let noRows = NoOfRows('Customers')-1;
for i=0 to $(noRows)
LET C = peek('vAddress', '$(i)', 'Customers');
if not WildMatch('$(b)','*, *') then
Data:
LOAD
'$(C)' as Address1,
[result/geometry/location/lat] as latitude,
[result/geometry/location/lng] as longitude,
[result/place_id]
FROM [http://maps.googleapis.com/maps/api/geocode/xml?address=$(C) &sensor=false] (XmlSimple, Table is [GeocodeResponse]);
end if
NEXT i;
Check it here too, Steps for beginners to implement Google Maps in Qlikview
Hi Settu
Thanks for your reply.
I have tried your code, but still only just got coordinates for one of my test customers.
But I found out what the issue was, just not yet how to fix it
Issue was and is now, that in Denmark we use some special letters such as Å,Ø,Æ, and when an address has one of theese letters the coordinates are not found.
So I have to find out how to get the service to use the Danish caracter set, any ideas ?
thx
NJ
You will have to URI / URL encode your parameter... then it will work
For instance using w3schools website gives this for the ÅØÆ and åøæ:
The pluses are the correct replacement for spaces actually...
%C3%85 is Å since it is two bytes in UTF-8 which is hex C3 and hex 85
The best is to use a function to do the translation as it will also cover accents and similar things like ü and ö...
If it is limited characters, may be try to use replace() function to replace the Danish Character to English Character..
Hi Petter
Thanks for your reply.
I think you are right, and it would also be nice to cover accents and similar things.
But unfortunately I don't know how to do this
Regards
NJ
This link should be helpful:
Howto: URL encoding in QlikView and Qlik Sense
Basically you can do it two ways:
A - Creating a simple JScript function that can be called directly from the Load Script
B - Making a MAPPING LOAD table
Then use MapSubString() function.