topic Re: Error while fetching [Lat,Long] from Address in QlikView

Error while fetching [Lat,Long] from Address

nikhilgarg — Wed, 25 Nov 2020 16:16:04 GMT

Hello All,

I am trying to achieve (Latitude and Longitude) from City, Address, State. But i am getting an error.

Pls. find attached .qvw and snapshot of the error. Can anyone help me in this?

Re: Error while fetching [Lat,Long] from Address

andrey_krylov — Wed, 17 Oct 2018 15:46:14 GMT

Hi. Try this solution, the same idea with another working web service

Working with maps from Mapquest

Mapquest Maps.qvw (194.2 K)Скачать

Re: Error while fetching [Lat,Long] from Address

nikhilgarg — Wed, 17 Oct 2018 16:29:53 GMT

Hey Andrey,

I am working on Qliksense. Will your solution works fine with qliksense as

well?

On Wed 17 Oct, 2018, 9:34 PM Andrey Krylov, <qcwebmaster@qlikview.com>

Re: Error while fetching [Lat,Long] from Address

andrey_krylov — Wed, 17 Oct 2018 17:09:09 GMT

I think so, try this code

Cities:

Load * Inline [

City, Qty

Sao Paulo, 100

Salvador, 150

Belo Horizonte, 110

Curitiba, 180

Rio de Janeiro, 200

Brasilia, 170 ];

let noRows = NoOfRows('Cities')-1;

for i=0 to $(noRows)

let a = peek('City',$(i),'Cities');

let b = peek('Qty',$(i),'Cities');

LOAD

'$(a)' as CustomerCity,

'$(b)' as CustomerQuantity,

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

Replace([results/result/locations/location/latLng/lat], '.', ',') as latitude,

Replace([results/result/locations/location/latLng/lng], '.', ',') as longitude

FROM [http://www.mapquestapi.com/geocoding/v1/address?&key=Fmjtd%7Cluur2l08nu%2Crl%3Do5-9a70d0&inFormat=json&json={"location":{"country": "BR", "city":"$(a)"}}&outFormat=xml] (XmlSimple, Table is [response]);

next i;

Drop table Cities;

Re: Error while fetching [Lat,Long] from Address

nikhilgarg — Sun, 21 Oct 2018 06:35:47 GMT

Thanks Andrey, this is working fine in Qlikview.

1: But in Qliksense, since we have to load data in form of "LIB://" and cannot use "From" clause, so can you help me in this w.r.t. Qliksense?

2: Also, does the value for "key(in URL)" remains constant for any data?

3: I have to use data for India. I guess Key value in your URL will differ for India. How can i get this "Key" value for India?

4: I am, trying to load street address and getting lat, long from this. I have used your script and renamed my street address to Cities and changed the country code from 'BR' to 'IN' in you URL. But it seems like, this is not working fine. Can you let em know where am i wrong? script i am using is:

Cities:

Load *,10 as Qty;

SQL SELECT Top 100 STREET

FROM "Qliksense_DB".dbo.Customer;

let noRows = NoOfRows('Cities')-1;

for i=0 to $(noRows)

let a = peek('City',$(i),'Cities');

let b = peek('Qty',$(i),'Cities');

LOAD

'$(a)' as CustomerCity,

'$(b)' as CustomerQuantity,

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

Replace([results/result/locations/location/latLng/lat], '.', ',') as latitude,

Replace([results/result/locations/location/latLng/lng], '.', ',') as longitude

FROM [http://www.mapquestapi.com/geocoding/v1/address?&key=Fmjtd%7Cluur2l08nu%2Crl%3Do5-9a70d0&inFormat=json&json={"location":{"country": "IN", "city":"$(a)"}}&outFormat=xml] (XmlSimple, Table is [response]);

next i;

Drop table Cities;

exit script;

Re: Error while fetching [Lat,Long] from Address

andrey_krylov — Sun, 21 Oct 2018 10:58:25 GMT

Hi Nikhil.

1. I don't work with Sense but about url loading see this post Re: Load Web File dynamically

2. I think any key remains constant

3. To get a key, simply access the site developers.mapquest.com and register yourself

4. And about location formats you'll find here Specifying Locations | MapQuest API Documentation