Adding to an exisiting table from a dynamic url

[#1]

LS.

This is the situation. I have a customer / postal-codes excel file. And I have a URL to a Google API with which I can get the Lat/Long coordinates when I feed it the postal codes. I don't know how to combine the two however.

Customers:
LOAD
 Description,
 Address,
 PostalCode,
 City
FROM
[..\Postalcodes.xls]
(biff, embedded labels, table is Page1$);
//And the google code:
GeoCode:
LOAD
 subfield([Response/Placemark/Point/coordinates], ',' ,1) AS Latitude,
 subfield([Response/Placemark/Point/coordinates], ',' ,2) AS Longitude
FROM [http://maps.google.com/maps/geo?q=Main+Street+133,+London,+UK&output=xml&oe=utf8&sensor=false&key=<ENTER YOUR API KEY>] (XmlSimple, Table is [kml]);

So the problem isn't getting the lat/long coordinates but getting them using the postal-code from the Customers table and writing the results back to the table (either a new table or the existing Customer table).

This is new material for me. Your help would be greatly appreciated!

Thanks is advance,

[#2]

Ok, I think I'm almost there...I have looped over the records looking up the coordinates creating a new table T:

Customers:
LOAD
 Description,
 Address,
 Zip,
 City
FROM
[.\Customers.xls]
(biff, embedded labels, table is Blad1$);

LET NumberOfCustomers = NoOfRows('Customers');
FOR i = 0 to $(NumberOfCustomers)-1
 LET C= peek('Zip', $(i), Customers);
 T:
 LOAD

 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),+NL&output=xml&oe=utf8&sensor=false&key=ABQIAAAAmDM9MXAc9fuktt5S3GsdNRTXWNiY2Ww2Y3H-zIQNjrA6EqkOGxRLrFhU2Z2ohjnalxL3UWgmdLbMuA] (XmlSimple, Table is [kml]);
NEXT

Can anyone tell me how to combine or link these two?

[#3]

Hi,

by combine you mean join this tables?

If so then just add C variable in T table and name the field Zip:

T:
 LOAD
 '$(C)' AS Zip
 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),+NL&output=xml&oe=utf8&sensor=false&key=ABQIAAAAmDM9MXAc9fuktt5S3GsdNRTXWNiY2Ww2Y3H-zIQNjrA6EqkOGxRLrFhU2Z2ohjnalxL3UWgmdLbMuA] (XmlSimple, Table is [kml]);

[disqr_rm · Accepted solution]

Here you go:

Customers:
LOAD Description,
Address,
Zip,
City
FROM
[.\Customers.xls](biff, embedded labels, table is Blad1$);

tmpzip:
LOAD DISTINCT Zip as vZip resident Customers;

LET NumberOfZips = FieldValueCount('vZip');
FOR i = 0 to $(NumberOfZips)-1
LET C = peek('vZip', '$(i)', 'tmpzip');
T:
LOAD
'$(C)' as Zip1,
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),+NL&output=xml&oe=utf8&sensor=false&key=ABQIAAAAmDM9MXAc9fuktt5S3GsdNRTXWNiY2Ww2Y3H-zIQNjrA6EqkOGxRLrFhU2Z2ohjnalxL3UWgmdLbMuA] (XmlSimple, Table is [kml]);
NEXT

LEFT JOIN (Customers)
LOAD Zip1 as Zip,
Latitude, Longitude
RESIDENT T;

drop tables T, tmpzip;

[#5]

Excellent! Thanks very much!

[#6]

Hi,

very impressive.

I wondfer if you could help me with this. I have an XML code that looks like this

FROM

[http://policeapi.rkh.co.uk/api/crime-area?key=cfcf42b6139de55218d8528d32fbb2a3&force=leicestershire&area=4] (XmlSimple, Table is [police-api])

I would like to extract data where the firld area is >0. I have tried it already but it seems not accepting either of the following symbols >, <, <>, >=, <+

Many thanks for your assistance in advance.

Andrea

;