7 Replies Latest reply: Oct 6, 2015 8:14 AM by Jonathan Poole RSS

    GeoCoding for QlikSense: Syntax error, missing/misplaced FROM

    David du Plessis

      Hi,

       

      I have come across a fantastic post to automatically geocode location addresses to a lat long using MapQuest API.

       

      I have converted the code to match my location information and have come to a final barrier that I cant seem to debug. My load script is as follows:

       

      GeoLocations:

      LIB CONNECT TO 'I--qliksense claim data-combined data.accdb';

      LOAD `Hospital_Provider_No` as LocationID,

          `l_addr_1`&' '&`l_addr_2`&','&`l_pcode`&','&`l_state`&','&`l_suburb`&','&`state_code` as GeoLocation,

          `l_suburb` as SearchCity,

          `l_addr_1`&' '&`l_addr_2` as Street,

          'AU' as SearchCountry,

          `l_pcode` as PostCode,

          `state_code` as State,

          name;

      SQL SELECT `Hospital_Provider_No`,

          `l_addr_1`,

          `l_addr_2`,

          `l_pcode`,

          `l_state`,

          `l_suburb`,

          name,

          `state_code`

      FROM `GeoCoding Hospital`;

       

      // Refer http://www.mapquestapi.com/geocoding/

      // number of seconds to wait for a URL datasource

      set OpenUrlTimeout=10;


      FOR i=0 to NoOfRows('GeoLocations')-1;

        LET vGeoLocation = peek( 'GeoLocation', $(i), 'GeoLocations');

        LET vLocationID = peek( 'LocationID', $(i), 'GeoLocations');

        LET vCity = peek( 'SearchCity', $(i), 'GeoLocations');

        LET vCountry = peek( 'SearchCountry', $(i), 'GeoLocations');

        LET vStreet = peek( 'Street', $(i), 'GeoLocations');

        LET vPostCode = peek( 'PostCode', $(i), 'GeoLocations');

        LET vState = peek( 'State', $(i), 'GeoLocations');


      Response:

      LOAD

        $(vLocationID) as LocationID,

          [results/result/locations/location/adminArea5] as City,

          [results/result/locations/location/adminArea3] as State,

          [results/result/locations/location/adminArea4] as County,

          [results/result/locations/location/adminArea1] as Country,

          [results/result/locations/location/geocodeQuality] as geocodeQuality,

          [results/result/locations/location/geocodeQualityCode] as geocodeQualityCode,

          [results/result/locations/location/dragPoint] as dragPoint,

          [results/result/locations/location/sideOfStreet] as sideOfStreet,

          [results/result/locations/location/linkId] as linkId,

          [results/result/locations/location/type] as [location/type],

          //[results/result/locations/location/mapUrl] as mapUrl,

          [results/result/locations/location/latLng/lat] as latitude,

          [results/result/locations/location/latLng/lng] as longitude,

          [results/result/providedLocation/location] as location,

          [info/statusCode] as statusCode//,

          //[info/copyright/text] as text

      FROM [http://www.mapquestapi.com/geocoding/v1/address?key=$(vKey)&callback=renderGeocode&outFormat=xml&city=$(vState)&country=$(vCountry)&street=$(vStreet)&postalCode=$(vPostCode)&state=$(vState)]

      (XmlSimple, Table is [response])

      Where isnull(statusCode) = 0;

        SLEEP 100;

      NEXT

       

      For some reasons this gives me the following error:

      Syntax error, missing/misplaced FROM: Response: LOAD 0031170L as LocationID, [results/result/locations/location/adminArea5] as City, [results/result/locations/location/adminArea3] as State, [results/result/locations/location/adminArea4] as County, [results/result/locations/location/adminArea1] as Country, [results/result/locations/location/geocodeQuality] as geocodeQuality, [results/result/locations/location/geocodeQualityCode] as geocodeQualityCode, [results/result/locations/location/dragPoint] as dragPoint, [results/result/locations/location/sideOfStreet] as sideOfStreet, [results/result/locations/location/linkId] as linkId, [results/result/locations/location/type] as [location/type], [results/result/locations/location/latLng/lat] as latitude, [results/result/locations/location/latLng/lng] as longitude, [results/result/providedLocation/location] as location, [info/statusCode] as statusCode FROM [http://www.mapquestapi.com/geocoding/v1/address?key=k61g7hfHGV04BOoze248pj7J5RrrSdgq&inFormat=xml&xml=<address&><location><street>PO Box 77 </street><city>TALLANGATTA</city><state>V</state> <postalCode>3700</postalCode></location></address>] (XmlSimple, Table is [response]) Where isnull(statusCode) = 0: Response: LOAD 0031170L as LocationID, [results/result/locations/location/adminArea5] as City, [results/result/locations/location/adminArea3] as State, [results/result/locations/location/adminArea4] as County, [results/result/locations/location/adminArea1] as Country, [results/result/locations/location/geocodeQuality] as geocodeQuality, [results/result/locations/location/geocodeQualityCode] as geocodeQualityCode, [results/result/locations/location/dragPoint] as dragPoint, [results/result/locations/location/sideOfStreet] as sideOfStreet, [results/result/locations/location/linkId] as linkId, [results/result/locations/location/type] as [location/type], [results/result/locations/location/latLng/lat] as latitude, [results/result/locations/location/latLng/lng] as longitude, [results/result/providedLocation/location] as location, [info/statusCode] as statusCode FROM [http://www.mapquestapi.com/geocoding/v1/address?key=k61g7hfHGV04BOoze248pj7J5RrrSdgq&inFormat=xml&xml=<address&><location><street>PO Box 77 </street><city>TALLANGATTA</city><state>V</state> <postalCode>3700</postalCode></location></address>] (XmlSimple, Table is [response]) Where isnull(statusCode) = 0


      I need to know if it is a syntax error on my part or an API issue (i.e. I am parsing the api parameters incorrectly.) Can anyone help?


      I came across this code on the following website:


      Geocoding and Maps visualizations | Qlikshow


      Cheers, David