Re: GeoCoding for QlikSense: Syntax error, missing... - Qlik Community

Report Inappropriate Content · ‎2015-10-01

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&cit...)]

(XmlSimple, Table is [response])

Where isnull(statusCode) = 0;

SLEEP 100;

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&xm... 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&xm... 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?

Cheers, David

mjm · ‎2015-10-02

Hello David,

As far as I see the error is because of a missing ';' after 'state_code'. As this is the last field to be loaded before the SQL statement, a ';' should be required.

Please try this. If this helps to resolve it, please mark my response and helpful to show it as the answer

JonnyPoole · ‎2015-10-02

David

unfortunately the data sourcing of the following dynamic WEB URL is not valid for Qlik Sense standard mode (the default mode for the qlik sense load script).

It IS valid for QlikView which is what this wonderful solution was written for.

FROM [http://www.mapquestapi.com/geocoding/v1/address?key=$(vKey)&callback=renderGeocode&outFormat=xml&cit...)]

(XmlSimple, Table is [response])

Now you can CHANGE qlik sense to run in legacy mode and it will accept this legacy syntax that worked in qlikview. the issue is that the data connections are no longer managed separately by Qlik Sense which is kind of need when you want to govern access to the data connections for different users. Also , changing to legacy mode will change for ALL your apps, its a global setting and you would have to change the rest of the FROM clauses in your scripts. Qlik sense will NOT auto-generate the correct legacy syntax as well making it difficult (unless you have personal edition of qlikview installed to author in parallel) to actually come up with the right script.

Ideally we find a Qlik Sense alternative to that script. Its an area that i'm interested in but haven't spent much time so i have little more to offer . I'll see if i can ping some folks to understand if / how we can introduce this scripting flexibility for dynamic data sources that you have brought up here.

-jonny

Clever_Anjos · ‎2015-10-02

Try

'$(vLocationID)' as LocationID, // Added quotes around vlocationID

Report Inappropriate Content · ‎2015-10-05

Hi Jonny,

Thanks for the reply. It is becoming increasingly difficult to understand what is for qliksense and qlikview.....

In relation to running in legacy mode what are the consequences? I dont have an issues with managing user access as all users of the data will be identical. I am more concerned about the changes to scripting and syntax causing issues. Is there a substantial difference?

Would really appreciate if you could ask a few people as having an ability to progressively geocode address data would be extremely powerful for qliksense. It is rare that address data from traditional business sources comes geocoded.

Cheers,

David

Report Inappropriate Content · ‎2015-10-05

Thanks Anjos,

Your response plus changing to legacy mode as per suggestion from jonny has allowed the script to work.

Cheers,

David

Report Inappropriate Content · ‎2015-10-05

Hi Mark,

Turns out it was a requirement to run in legacy mode, as per below from Jonny, and the quotation marks around vlocation as per Ajos post.

Thanks for the help.

JonnyPoole · ‎2015-10-06

Its not the biggest deal in the world and ideally its for a temporary need. To fulfill a temporary need, i would suggest installing QlikView personal edition , form the script in QlikView and then copy/paste it over to Qlik Sense. Once Qlik Sense is configured to run in legacy mode, it will accept QlikView script verbatim.

If the need is long term and you become a little familiar with the script, its not going to be difficult to modify the CONNECT (qlikview only) statements and the FROM clauses in the QlikView formed script to reference the Qlik Sense connections using the LIB syntax (qlik sense only) . Those are the primary differences between QlikView and Qlik Sense. The rest (99%) of the script is identical. I just want to set your expectations appropriately.

I don't disagree with you on the geocoding. I may have seen a recent solution and i'll see what we have planned.

GeoCoding for QlikSense: Syntax error, missing/misplaced FROM

Script