Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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
Not applicable

Re: Load Data from gisgraphy / xml source

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

2 Replies
Not applicable

Re: Load Data from gisgraphy / xml source

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

Not applicable

Re: Load Data from gisgraphy / xml source

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

I never thought of a loading - method like that.

Thanks!

Community Browser