Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Reverse Geocoding and for...next loop with inner

Hi friends,

I'm trying to get the physical address for my longitude and latitude values. When I use my code with only one location it's working fine. The problem comes when I want to use this code into a For...Next loop. In this case, the row calculated in each interation doesn't go to the same resident table (when I do this in other contexts, the rows calculated in each interation go to the same resident table like an append). How can I do this???

I'm loading my locations from xlsx file. For each of these locations I'm getting the different parts of the address

LatlongProximityAddress_TypeValue
37.5442706,-4.7277528APPROXIMATEadministrative_area_level_1Andalusia
37.5442706,-4.7277528APPROXIMATEadministrative_area_level_2Córdoba
37.5442706,-4.7277528APPROXIMATEcountrySpain
37.5442706,-4.7277528APPROXIMATElocalityMontilla
37.5442706,-4.7277528APPROXIMATErouteCV-240

then, I'm loading each address in one row

LatlongAddressProximityCountryArea1Area2LocalityPostal_CodeRouteStreet_number
37.5442706,-4.7277528CV-240, Montilla, Córdoba, SpainAPPROXIMATESpainAndalusiaCórdobaMontilla CV-240


When the number of locations is greater than one, the load sentence into the loop generates many resident tables.

One Location.JPG             Many Locations.JPG                     

/////////////////// My code ////////////////////////

Location:

load Location,

     Longitude,

     Latitude,

     Latitude&','&Longitude as Latlong

from Locations.xlsx (ooxml, embedded labels, table is Sheet1);

for i = 0 to NoOfRows('Location') -1

          let v_latlong = peek('Latlong', $(i), 'Location');

          Aux:

          first 1 // There are several results, but we only want the first one

          load '$(v_latlong)' as Latlong,

                       formatted_address as Address,

                       [geometry/location_type] as Proximity,

                       %Key_result_93EFE47D0E261AF3

          from [http://maps.google.com/maps/api/geocode/xml?latlng=$(v_latlong)&oe=utf8&sensor=false] (XmlSimple, Table is [GeocodeResponse/result]);

          inner join

          load %Key_result_93EFE47D0E261AF3,

               long_name as Value,

               %Key_address_component_3D78D9B402C68EA5

          from [http://maps.google.com/maps/api/geocode/xml?latlng=$(v_latlong)&oe=utf8&sensor=false] (XmlSimple, Table is [GeocodeResponse/result/address_component]);

          inner join

          load type%Table as Address_Type,

               %Key_address_component_3D78D9B402C68EA5 as %Key_address_component_3D78D9B402C68EA5

          from [http://maps.google.com/maps/api/geocode/xml?latlng=$(v_latlong)&oe=utf8&sensor=false] (XmlSimple, Table is [GeocodeResponse/result/address_component/type])

          where type%Table <> 'political';

     

next

// Load each address in one row

Result:

load Latlong as aLatlong,

     Address as aAddress,

     Proximity as aProximity,

     Max      (if(Address_Type = 'street_number'              , Value)) as Street_number,

     MaxString(if(Address_Type = 'route'                      , Value)) as Route,

     Max      (if(Address_Type = 'postal_code'                , Value)) as Postal_Code,

     MaxString(if(Address_Type = 'locality'                   , Value)) as Locality,

     MaxString(if(Address_Type = 'administrative_area_level_2', Value)) as Area2,

     MaxString(if(Address_Type = 'administrative_area_level_1', Value)) as Area1,

     MaxString(if(Address_Type = 'country'                    , Value)) as Country

resident Aux

group by Latlong,

         Address,

         Proximity;

/////////////////// My code ////////////////////////


1 Solution

Accepted Solutions
rperezlaso
New Contributor III

Re: Reverse Geocoding and for...next loop with inner

Try this code please, this is the solution:

Location:
load Location,
Longitude,
Latitude,
Latitude&','&Longitude as Latlong
from Locations.xlsx (ooxml, embedded labels, table is Sheet1);

Table:
load * Inline [Latlong];

for i = 0 to NoOfRows('Location') -1

let v_latlong = peek('Latlong', $(i), 'Location');

Aux:
first 1 // There are several results, but we only want the first one
load '$(v_latlong)' as Latlong,
 
formatted_address as Address,
 
[geometry/location_type] as Proximity,
 
%Key_result_93EFE47D0E261AF3
from [http://maps.google.com/maps/api/geocode/xml?latlng=$(v_latlong)&oe=utf8&sensor=false] (XmlSimple, Table is [GeocodeResponse/result]);

inner join
load %Key_result_93EFE47D0E261AF3,
long_name as Value,
%Key_address_component_3D78D9B402C68EA5
from [http://maps.google.com/maps/api/geocode/xml?latlng=$(v_latlong)&oe=utf8&sensor=false] (XmlSimple, Table is [GeocodeResponse/result/address_component]);

inner join
load type%Table as Address_Type,
%Key_address_component_3D78D9B402C68EA5 as %Key_address_component_3D78D9B402C68EA5
from [http://maps.google.com/maps/api/geocode/xml?latlng=$(v_latlong)&oe=utf8&sensor=false] (XmlSimple, Table is [GeocodeResponse/result/address_component/type])
where type%Table <> 'political';

Concatenate(Table)
Load *
resident Aux;

drop table Aux;

next

// Load each address in one row
Result:
load Latlong as aLatlong,
Address as aAddress,
Proximity as aProximity,
Max      (if(Address_Type = 'street_number' , Value)) as Street_number,
MaxString(if(Address_Type = 'route' , Value)) as Route,
Max      (if(Address_Type = 'postal_code' , Value)) as Postal_Code,
MaxString(if(Address_Type = 'locality' , Value)) as Locality,
MaxString(if(Address_Type = 'administrative_area_level_2', Value)) as Area2,
MaxString(if(Address_Type = 'administrative_area_level_1', Value)) as Area1,
MaxString(if(Address_Type = 'country' , Value)) as Country
resident Table
group by Latlong,
Address,
Proximity;

10 Replies
jonbrough
Valued Contributor

Re: Reverse Geocoding and for...next loop with inner

Create a placeholder for the Aux table before the For loop starts, then concatenate into it.

Jonathan

Not applicable

Re: Reverse Geocoding and for...next loop with inner

Thanks for the answer jonbroughavone but I don't know what is a placeholder. Could you explain me how to use it???

jonbrough
Valued Contributor

Re: Reverse Geocoding and for...next loop with inner

Sorry, think that might just be my name for it.

Create a blank Aux table, using an inline statement like this:

Aux:

LOAD * INLINE [

   Latlong

];

Create with now rows, and you don't have to include all the field names (in fact it would be better not to in case you change them later).

You can then concatenate into this table instead of needing to create it every loop. This would be with this sort of syntax:

CONCATENATE (Aux) LOAD

....

This should stop the multiple tables appearing at least.

Jonathan

Not applicable

Re: Reverse Geocoding and for...next loop with inner

This solution stops the multiple tables appearing, but the table Aux is empty. The implementation is in the attached file.

jonbrough
Valued Contributor

Re: Reverse Geocoding and for...next loop with inner

Aux table has 7 rows in the file you sent.

Jonathan

Not applicable

Re: Reverse Geocoding and for...next loop with inner

Aux table has 0 rows in the file (7 fields).

Tables.JPG

jonbrough
Valued Contributor

Re: Reverse Geocoding and for...next loop with inner

Strange. Are you sure you sent me the same file.

See Document settings > Tables:

Doc settings Tables.png

See the preview of the table viewer:

Table Viewer.png

Not applicable

Re: Reverse Geocoding and for...next loop with inner

Ok jonbroughavone. The file is the same, but the data loaded no. The example I've set you (like my first attached file) works fine with only one location. If you reload the data you could see the problem. Sorry for my explanations and thanks for your answers.

rperezlaso
New Contributor III

Re: Reverse Geocoding and for...next loop with inner

Try this code please, this is the solution:

Location:
load Location,
Longitude,
Latitude,
Latitude&','&Longitude as Latlong
from Locations.xlsx (ooxml, embedded labels, table is Sheet1);

Table:
load * Inline [Latlong];

for i = 0 to NoOfRows('Location') -1

let v_latlong = peek('Latlong', $(i), 'Location');

Aux:
first 1 // There are several results, but we only want the first one
load '$(v_latlong)' as Latlong,
 
formatted_address as Address,
 
[geometry/location_type] as Proximity,
 
%Key_result_93EFE47D0E261AF3
from [http://maps.google.com/maps/api/geocode/xml?latlng=$(v_latlong)&oe=utf8&sensor=false] (XmlSimple, Table is [GeocodeResponse/result]);

inner join
load %Key_result_93EFE47D0E261AF3,
long_name as Value,
%Key_address_component_3D78D9B402C68EA5
from [http://maps.google.com/maps/api/geocode/xml?latlng=$(v_latlong)&oe=utf8&sensor=false] (XmlSimple, Table is [GeocodeResponse/result/address_component]);

inner join
load type%Table as Address_Type,
%Key_address_component_3D78D9B402C68EA5 as %Key_address_component_3D78D9B402C68EA5
from [http://maps.google.com/maps/api/geocode/xml?latlng=$(v_latlong)&oe=utf8&sensor=false] (XmlSimple, Table is [GeocodeResponse/result/address_component/type])
where type%Table <> 'political';

Concatenate(Table)
Load *
resident Aux;

drop table Aux;

next

// Load each address in one row
Result:
load Latlong as aLatlong,
Address as aAddress,
Proximity as aProximity,
Max      (if(Address_Type = 'street_number' , Value)) as Street_number,
MaxString(if(Address_Type = 'route' , Value)) as Route,
Max      (if(Address_Type = 'postal_code' , Value)) as Postal_Code,
MaxString(if(Address_Type = 'locality' , Value)) as Locality,
MaxString(if(Address_Type = 'administrative_area_level_2', Value)) as Area2,
MaxString(if(Address_Type = 'administrative_area_level_1', Value)) as Area1,
MaxString(if(Address_Type = 'country' , Value)) as Country
resident Table
group by Latlong,
Address,
Proximity;

Community Browser