Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
IMPORTANT: Upcoming LEF Database Maintenance, Oct. 3rd - SEE DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Load Data from gisgraphy / xml source

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!

1 Solution

Accepted Solutions
Highlighted
Not applicable

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

View solution in original post

2 Replies
Highlighted
Not applicable

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

View solution in original post

Highlighted
Not applicable

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

I never thought of a loading - method like that.

Thanks!