topic Re: Reverse Geocoding and for...next loop with inner in QlikView

Reverse Geocoding and for...next loop with inner

Wed, 16 Jan 2013 12:11:19 GMT

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

Latlong	Proximity	Address_Type	Value
37.5442706,-4.7277528	APPROXIMATE	administrative_area_level_1	Andalusia
37.5442706,-4.7277528	APPROXIMATE	administrative_area_level_2	Córdoba
37.5442706,-4.7277528	APPROXIMATE	country	Spain
37.5442706,-4.7277528	APPROXIMATE	locality	Montilla
37.5442706,-4.7277528	APPROXIMATE	route	CV-240

then, I'm loading each address in one row

Latlong	Address	Proximity	Country	Area1	Area2	Locality	Postal_Code	Route	Street_number
37.5442706,-4.7277528	CV-240, Montilla, Córdoba, Spain	APPROXIMATE	Spain	Andalusia	Córdoba	Montilla		CV-240

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

/////////////////// 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';

// 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 ////////////////////////

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

Anonymous — Wed, 16 Jan 2013 12:16:56 GMT

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

Jonathan

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

Wed, 16 Jan 2013 12:22:37 GMT

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

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

Anonymous — Wed, 16 Jan 2013 12:40:00 GMT

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

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

Wed, 16 Jan 2013 13:26:23 GMT

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

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

Anonymous — Wed, 16 Jan 2013 14:13:34 GMT

Aux table has 7 rows in the file you sent.

Jonathan

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

Wed, 16 Jan 2013 14:32:01 GMT

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

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

Anonymous — Wed, 16 Jan 2013 15:36:41 GMT

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

See Document settings > Tables:

See the preview of the table viewer:

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

Wed, 16 Jan 2013 15:59:08 GMT

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.

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

rperezlaso — Wed, 16 Jan 2013 16:23:03 GMT

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;

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

Wed, 16 Jan 2013 16:29:42 GMT

It works perfect. Thaks jonbroughavone and rperezlaso.