Load address from Excel then get Longitude/Latitude

[#1]

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!

[#2]

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])

[#3 · Accepted solution]

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

[#4]

I get a blank row, but avoid it using:

     for i=0 to $(noRows)-1

Thanks for your code

[#5]

rhedgpeth wrote:

Also, are there "

" tags or something for this forum that I'm just not seeing?

How-To: Add Code to your Content

[#6]

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

[#7]

[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 ?

[#8]

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]

[ksmith24]

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

[Anonymous]

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?