I need a better way to search lat and long without use a 'For'-SQL DB

Mon, 26 Jan 2026 16:26:21 GMT

English version:

To search for latitude and longitude (coordinates) for create a google map at qlikview, I'm using a 'For' that goes line by line from my table to use the "GeocodeResponse'.

But my table is giant, because I use a sql database. You can see how i did it right below.

Question: Is there a better way to use this method without go record by record from the table?

---------------------------------

Portuguese version:

Para criar minha tabela de clientes com latitude e longitude usando busca do google, Utilizo de um For para percorrer registro por registro da minha tabela e buscar as coordenadas usando GeocodeResponse, conforme o exemplo abaixo. Como uso banco de dados sql server, a minha base de dados é enorme, não posso ficar gerando linha por linha.

Pergunta: Alguém pode me indicar uma maneira mais prática e rápida de buscar estas informações, sem ser por esta forma?

---------------------------------

Exemplo:

// Frete is my table from sql server database.

Let noRows = NoOfRows('Frete')-1;

For i=0 To $(noRows)

Let a = peek('CliDev',$(i),'Frete');

Let b = peek('Devedor',$(i),'Frete');

Let c = peek('CidadeDev',$(i),'Frete');

Let d = peek('UFDev',$(i),'Frete');

GeocodeResponse:

LOAD

'$(a)' as CliDeveM,

'$(b)' as DevedorMa,

'$(c)' as CidDevM,

'$(d)' as UFDevM,

([result/geometry/location/lat]) as Latitude,

([result/geometry/location/lng]) as Longitude

FROM [http://maps.googleapis.com/maps/api/geocode/xml?address=CidDevM&sensor=false] (XmlSimple, Table is [GeocodeResponse]);

next i;

Re: I need a better way to search lat and long without use a 'For'-SQL DB

Alexander_Thor — Mon, 12 May 2014 03:09:09 GMT

I would recommend that you store the already geocoded address into a qvd and only geocode new addresses on reload.

Sadly Google don't allow you to geocode in batch.

Re: I need a better way to search lat and long without use a 'For'-SQL DB

Thu, 22 May 2014 17:58:22 GMT

Thank you Alexander,

That was a good idea. I got all the coordinates from google maps and I saved in one qvd, just like you said.

For all my others reports, I'll use that one.

topic I need a better way to search lat and long without use a 'For'-SQL DB in QlikView

I need a better way to search lat and long without use a 'For'-SQL DB

Re: I need a better way to search lat and long without use a 'For'-SQL DB

Re: I need a better way to search lat and long without use a 'For'-SQL DB