Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I've found a few sources on loading Excel data into QlikView:
LOAD
Address
FROM
C:\Users\username\Desktop\Addresses.xlsx
(ooxml, embedded labels, table is Sheet1);
and a few other sources on getting the longitude/latitude values via Google GeoCoding:
LOAD
'$(C)' as Address,
subfield([Response/Placemark/Point/coordinates], ',' ,1) AS latitude,
subfield([Response/Placemark/Point/coordinates], ',' ,2) AS longitude
FROM [http://maps.google.com/maps/geo?q=$(C)&output=xml&oe=utf8&sensor=false&key=XXYYZZ] (XmlSimple, Table is [Sheet1]);
...but how would I do both? Meaning, how can I first load the addresses into QlikView, and then convert those addresses into longitude/latitude values? A subquery of some sort? Join the loads?
Thanks for any help!
I was able to resolve the issue after fixing a few trivial issues (see solution below). The process for resolving these issues (I've had a few others besides this one) has been irritating to say the least as the documentation and debugging tool for QlikView scripting is pretty sub-par in my opinion. Are these limitations improved in the full (paid-for) version of the product?
Solution:
Table1:
LOAD
City,
Population,
Address
FROM
C:\Users\username\Desktop\Location4.xlsx
(ooxml, embedded labels, table is Sheet1);
let noRows = NoOfRows('Table1');
for i=0 to $(noRows)
let c=peek('City',$(i),'Table1');
let p=peek('Population',$(i),'Table1');
let a=peek('Address',$(i),'Table1');
Data:
LOAD
'$(c)' as City,
'$(p)' as Population,
'$(a)' as Address,
subfield([Response/Placemark/Point/coordinates], ',' ,1) AS longitude,
subfield([Response/Placemark/Point/coordinates], ',' ,2) AS latitude
FROM [http://maps.google.com/maps/geo?q=$(a)&output=xml&oe=utf8&sensor=false&key=XXYYZZ] (XmlSimple, Table is [kml]);
next
Update:
I believe I am close to a solution, but I am getting an error (see bottom of reply) with the following:
Table1:
LOAD
City,
Population,
Address
FROM
C:\Users\username\Desktop\Location4.xlsx
(ooxml, embedded labels, table is Sheet1);
//Data:
let noRows = NoOfRows('Table1');
for i=0 to $(noRows)
let city = peek('City',$(i),'Table1');
let population = peek('Population',$(i),'Table1');
let address = peek('Address',$(i),'Table1');
Data:
LOAD
$(city) as City,
$(population) as Population,
$(address) as Address,
subfield([Response/Placemark/Point/coordinates], ',' ,1) AS latitude,
subfield([Response/Placemark/Point/coordinates], ',' ,2) AS longitude
FROM [http://maps.google.com/maps/geo?q=$(address)&output=xml&oe=utf8&sensor=false&key=XXYYZZ] (XmlSimple, Table is [Sheet2]);
next
I am, however, seeing that the values are looping through correctly.
Also, are there "[code]" tags or something for this forum that I'm just not seeing?
Thanks!
ERROR:
Syntax error, missing/misplaced FROM:
Data:
LOAD
Somewhere, MO as City,
2000000 as Population,
1234+Main+Ave+Somewhere+MO as Address,
subfield([Response/Placemark/Point/coordinates], ',' ,1) AS latitude,
subfield([Response/Placemark/Point/coordinates], ',' ,2) AS longitude
FROM [http://maps.google.com/maps/geo?q=1234+Main+Ave+Somewhere+MO&output=xml&oe=utf8&sensor=false&key=XXY...] (XmlSimple, Table is [Sheet2])
I was able to resolve the issue after fixing a few trivial issues (see solution below). The process for resolving these issues (I've had a few others besides this one) has been irritating to say the least as the documentation and debugging tool for QlikView scripting is pretty sub-par in my opinion. Are these limitations improved in the full (paid-for) version of the product?
Solution:
Table1:
LOAD
City,
Population,
Address
FROM
C:\Users\username\Desktop\Location4.xlsx
(ooxml, embedded labels, table is Sheet1);
let noRows = NoOfRows('Table1');
for i=0 to $(noRows)
let c=peek('City',$(i),'Table1');
let p=peek('Population',$(i),'Table1');
let a=peek('Address',$(i),'Table1');
Data:
LOAD
'$(c)' as City,
'$(p)' as Population,
'$(a)' as Address,
subfield([Response/Placemark/Point/coordinates], ',' ,1) AS longitude,
subfield([Response/Placemark/Point/coordinates], ',' ,2) AS latitude
FROM [http://maps.google.com/maps/geo?q=$(a)&output=xml&oe=utf8&sensor=false&key=XXYYZZ] (XmlSimple, Table is [kml]);
next
I get a blank row, but avoid it using:
for i=0 to $(noRows)-1
Thanks for your code
rhedgpeth wrote:
Also, are there "
" tags or something for this forum that I'm just not seeing?
I'm sorry to necro a little old thread.
But i use this code and my database consists of about 90k records, it takes about 8 hours to process this number of records. The machine is a quad core 4gb machine so im assuming its the grabbing of the data from google that's causing such a long amount of time to be required.
Any idea's how we could get this faster ?
Ta
Adam
[http://maps.google.com/maps/geo?q=$(a)&output=xml&oe=utf8&sensor=false&key=XXYYZZ]
this link is not working can any one update me on the same ?
yes.. It seems the link doesn't work now....
[http://maps.google.com/maps/geo?q=$(a)&output=xml&oe=utf8&sensor=false&key=XXYYZZ]
The Google Geocoding API - Google Maps API Web Services — Google Developers
https://developers.google.com/maps/documentation/geocoding/
"Note that the Geocoding API v2 has been turned down and is no longer available. Users of the Geocoding API v2 service should upgrade to v3."
The following syntax should work as of Oct 2014 (following the previously described syntax, with just a few modifications - note you will need a valid KEY)
LOAD
[result/geometry/location/lat] AS latitude,
[result/geometry/location/lng] AS longitude
FROM [https://maps.googleapis.com/maps/api/geocode/xml?address=$(a) &key=XXYYZZ] (XmlSimple, Table is [GeocodeResponse]);
It's still fairly slow though...
Keith. I see You are able to use http request to google api. What have You done to do this. I have only error because this. Can You help me with this?