topic Load Data from gisgraphy / xml source in QlikView

Load Data from gisgraphy / xml source

Tue, 07 Jan 2014 10:55:48 GMT

Hello QV User's and Developers!

Im looking for a hint or solution to load data from a web-source (gisgraphy, example: http://services.gisgraphy.com/geocoding/geocode?country=AT&address=1010 ) .. (of course i set up my own server, but this public server is for example, so please use it with care), result is xml: example:

<results><numFound>1</numFound><QTime>86</QTime><result><id>2761369</id><lng>16.372079849243164</lng><lat>48.20848846435547</lat><geocodingLevel>CITY</geocodingLevel><name>Vienna</name><zipCode>1010</zipCode><city>Vienna</city><state>Politischer Bezirk Wien (Stadt)</state><countryCode>AT</countryCode></result></results>

i have a table with postcodes, and i want to join or create a new table with long and lat from that source.

i need to query every entry in that postcodes table against my gisgaphy server, is that possible with QV?

Thank you soo much for every little hint!

Re: Load Data from gisgraphy / xml source

Thu, 09 Jan 2014 14:19:31 GMT

As of my understanding, you want load the latitude & longitude for each ZipCode in the other table.

For this, we can create the FOR Loop for each ZipCode and load the Latitude & Longitude from XML source.

Please find the below script:

TABLE: // Lets Assume this table having ZipCodes

LOAD * INLINE [

ZipCode

1010

];

// Creating the Loop for each ZipCode and load the respective xml file.

FOR i= 1 to FieldValueCount('ZipCode')

LET vZipCode = FieldValue('ZipCode',$(i)) ;

// Start of [geocode?country=AT&address=1010.com/geocoding/geocode?country=at&address=1010] LOAD statements

results:

LOAD numFound,

QTime,

[result/id] as id,

[result/lng] as lng,

[result/lat] as lat,

[result/geocodingLevel] as geocodingLevel,

[result/name] as name,

[result/zipCode] as ZipCode,

[result/city] as city,

[result/state] as state,

[result/countryCode] as countryCode

FROM [http://services.gisgraphy.com/geocoding/geocode?country=AT&address=$(vZipCode)] (XmlSimple, Table is [results]);

//FROM [http://services.gisgraphy.com/geocoding/geocode?country=AT&address=1010] (XmlSimple, Table is [results]);

// End of [geocode?country=AT&address=1010.com/geocoding/geocode?country=at&address=1010] LOAD statements

NEXT i

Re: Load Data from gisgraphy / xml source

Thu, 09 Jan 2014 14:47:14 GMT

Wow, Thank you soo much, i have not thought that it is thaaaat easy.

I never thought of a loading - method like that.

Thanks!