Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW

Splitting up GeoAnalytics connector operations

No ratings
cancel
Showing results for 
Search instead for 
Did you mean: 
Sonja_Bauernfeind
Digital Support
Digital Support

Splitting up GeoAnalytics connector operations

Last Update:

Jul 1, 2021 6:07:34 AM

Updated By:

Sonja_Bauernfeind

Created date:

Nov 6, 2018 9:38:02 AM

Many of the GeoAnalytics can be executed with a split input of the indata table. This article explains which and how to modify the code that the connector produces. Operations that cannot be Splittable are mostly the aggregating and hence not Splittable. When loadable tables are used for input, inline tables are created in loops and can be used for a quick way to split. Of course it's possible to write custom code to do the splitting instead.

Making calls with large indata tables often causes time outs on the server side, splitting is a way around that. 

 

Environment:

Qlik GeoAnalytics 

 

Splittable ops Non-Splittable ops Special ops, splittable
  • AddressPointLookup
  • Intersects
  • IpLookup
  • NamedAreaLookup
  • NamedPointLookup
  • PointToAddressLookup
  • Routes
  • TravelAreas
  • Within
  • Closest
  • Cluster 
  • Dissolve
  • Load
  • IntersectsMost 
  • Simplify
  • Binning
  • SpatialIndex

 

Binning and SpatialIndex

Bining and SpatialIndex differs from other operations, they are not placing any call to the server if the indata are internal geometries, ie lat ,ong points. The operations als produce the same type of results so the resulting tables can be concatenated.
 

 

Resolution:

 

Example, a Within operation

Before the edit

The code as the connector produces it:

/* Generated by Idevio GeoAnalytics for operation Within ---------------------- */
Let [EnclosedInlineTable] = 'POSTCODE' & Chr(9) & 'Postal.Latitude' & Chr(9) & 'Postal.Longitude';
Let numRows = NoOfRows('PostalData');
Let chunkSize = 1000;
Let chunks = numRows/chunkSize;
For n = 0 to chunks
   Let chunkText = '';
   Let chunk = n*chunkSize;
   For i = 0 To chunkSize-1
      Let row = '';
      Let rowNr = chunk+i;
      Exit for when rowNr >= numRows;
      For Each f In 'POSTCODE', 'Postal.Latitude', 'Postal.Longitude'
         row = row & Chr(9) & Replace(Replace(Replace(Replace(Replace(Replace(Peek('$(f)', $(rowNr), 'PostalData'), Chr(39), '\u0027'), Chr(34), '\u0022'), Chr(91), '\u005b'), Chr(47), '\u002f'), Chr(42), '\u002a'), Chr(59), '\u003b');
      Next
      chunkText = chunkText & Chr(10) & Mid('$(row)', 2);
   Next
   [EnclosedInlineTable] = [EnclosedInlineTable] & chunkText;
Next
chunkText=''


Let [EnclosingInlineTable] = 'ClubCode' & Chr(9) & 'Car5mins_TravelArea';
Let numRows = NoOfRows('TravelAreas5');
Let chunkSize = 1000;
Let chunks = numRows/chunkSize;
For n = 0 to chunks
   Let chunkText = '';
   Let chunk = n*chunkSize;
   For i = 0 To chunkSize-1
      Let row = '';
      Let rowNr = chunk+i;
      Exit for when rowNr >= numRows;
      For Each f In 'ClubCode', 'Car5mins_TravelArea'
         row = row & Chr(9) & Replace(Replace(Replace(Replace(Replace(Replace(Peek('$(f)', $(rowNr), 'TravelAreas5'), Chr(39), '\u0027'), Chr(34), '\u0022'), Chr(91), '\u005b'), Chr(47), '\u002f'), Chr(42), '\u002a'), Chr(59), '\u003b');
      Next
      chunkText = chunkText & Chr(10) & Mid('$(row)', 2);
   Next
[EnclosingInlineTable] = [EnclosingInlineTable] & chunkText;
Next
chunkText=''


[WithinAssociations]:
SQL SELECT [POSTCODE], [ClubCode] FROM Within(enclosed='Enclosed', enclosing='Enclosing')
DATASOURCE Enclosed INLINE tableName='PostalData', tableFields='POSTCODE,Postal.Latitude,Postal.Longitude', geometryType='POINTLATLON', loadDistinct='NO', suffix='', crs='Auto' {$(EnclosedInlineTable)}
DATASOURCE Enclosing INLINE tableName='TravelAreas5', tableFields='ClubCode,Car5mins_TravelArea', geometryType='POLYGON', loadDistinct='NO', suffix='', crs='Auto' {$(EnclosingInlineTable)}
SELECT [POSTCODE], [Enclosed_Geometry] FROM Enclosed
SELECT [ClubCode], [Car5mins_TravelArea] FROM Enclosing;
[EnclosedInlineTable] = '';
[EnclosingInlineTable] = '';

/* End Idevio GeoAnalytics operation Within ----------------------------------- */

 

After edit

The header and the call is moved inside of the loop. chunkSize decides how big each split is.

Note that the first inline table now comes after the first one, this to get the call inside of the iteration.

/* Generated by Idevio GeoAnalytics for operation Within ---------------------- */

Let [EnclosingInlineTable] = 'ClubCode' & Chr(9) & 'Car5mins_TravelArea';
Let numRows = NoOfRows('TravelAreas5');
Let chunkSize = 1000;
Let chunks = numRows/chunkSize;
For n = 0 to chunks
   Let chunkText = '';
   Let chunk = n*chunkSize;
   For i = 0 To chunkSize-1
      Let row = '';
      Let rowNr = chunk+i;
      Exit for when rowNr >= numRows;
      For Each f In 'ClubCode', 'Car5mins_TravelArea'
         row = row & Chr(9) & Replace(Replace(Replace(Replace(Replace(Replace(Peek('$(f)', $(rowNr), 'TravelAreas5'), Chr(39), '\u0027'), Chr(34), '\u0022'), Chr(91), '\u005b'), Chr(47), '\u002f'), Chr(42), '\u002a'), Chr(59), '\u003b');
      Next
      chunkText = chunkText & Chr(10) & Mid('$(row)', 2);
   Next
   [EnclosingInlineTable] = [EnclosingInlineTable] & chunkText;
Next
chunkText=''

Let numRows = NoOfRows('PostalData');

Let chunkSize = 1000;
Let chunks = numRows/chunkSize;
For n = 0 to chunks

   Let [EnclosedInlineTable] = 'POSTCODE' & Chr(9) & 'Postal.Latitude' & Chr(9) & 'Postal.Longitude';

   Let chunkText = '';
   Let chunk = n*chunkSize;
   For i = 0 To chunkSize-1
      Let row = '';
      Let rowNr = chunk+i;
      Exit for when rowNr >= numRows;
      For Each f In 'POSTCODE', 'Postal.Latitude', 'Postal.Longitude'
         row = row & Chr(9) & Replace(Replace(Replace(Replace(Replace(Replace(Peek('$(f)', $(rowNr), 'PostalData'), Chr(39), '\u0027'), Chr(34), '\u0022'), Chr(91), '\u005b'), Chr(47), '\u002f'), Chr(42), '\u002a'), Chr(59), '\u003b');
      Next
      chunkText = chunkText & Chr(10) & Mid('$(row)', 2);
   Next
   [EnclosedInlineTable] = [EnclosedInlineTable] & chunkText;

   [WithinAssociations]:
   SQL SELECT [POSTCODE], [ClubCode] FROM Within(enclosed='Enclosed', enclosing='Enclosing')
   DATASOURCE Enclosed INLINE tableName='PostalData', tableFields='POSTCODE,Postal.Latitude,Postal.Longitude', geometryType='POINTLATLON', loadDistinct='NO', suffix='', crs='Auto' {$(EnclosedInlineTable)}
   DATASOURCE Enclosing INLINE tableName='TravelAreas5', tableFields='ClubCode,Car5mins_TravelArea', geometryType='POLYGON', loadDistinct='NO', suffix='', crs='Auto' {$(EnclosingInlineTable)}
   SELECT [POSTCODE], [Enclosed_Geometry] FROM Enclosed
   SELECT [ClubCode], [Car5mins_TravelArea] FROM Enclosing;
   [EnclosedInlineTable] = '';
   [EnclosingInlineTable] = '';

Next
chunkText=''
/* End Idevio GeoAnalytics operation Within ----------------------------------- */

 

Example, a AddressPointLookup operation

Before the edit

The code as the connector produces it:

/* Generated by GeoAnalytics for operation AddressPointLookup ---------------------- */
Let [DatasetInlineTable] = 'id' & Chr(9) & 'STREET_NAME' & Chr(9) & 'STREET_NUMBER';
Let numRows = NoOfRows('data');
Let chunkSize = 1000;
Let chunks = numRows/chunkSize;
For n = 0 to chunks
   Let chunkText = '';
   Let chunk = n*chunkSize;
   For i = 0 To chunkSize-1
      Let row = '';
      Let rowNr = chunk+i;
      Exit for when rowNr >= numRows;
      For Each f In 'id', 'STREET_NAME', 'STREET_NUMBER'
         row = row & Chr(9) & Replace(Replace(Replace(Replace(Replace(Replace(Peek('$(f)', $(rowNr), 'data'), Chr(39), '\u0027'), Chr(34), '\u0022'), Chr(91), '\u005b'), Chr(47), '\u002f'), Chr(42), '\u002a'), Chr(59), '\u003b');
      Next
      chunkText = chunkText & Chr(10) & Mid('$(row)', 2);
   Next 
   [DatasetInlineTable] = [DatasetInlineTable] & chunkText; 
Next
chunkText=''
[AddressPointLookupResult]:
SQL SELECT [id], [Dataset_Address], [Dataset_Geometry], [CountryIso2], [Dataset_Adm1Code], [Dataset_City], [Dataset_PostalCode], [Dataset_Street], [Dataset_HouseNumber], [Dataset_Match] 
FROM AddressPointLookup(searchTextField='', country='"Canada"', stateField='', cityField='"Toronto"', postalCodeField='', streetField='STREET_NAME', houseNumberField='STREET_NUMBER', matchThreshold='0.5', service='default', dataset='Dataset')
DATASOURCE Dataset INLINE tableName='data', tableFields='id,STREET_NAME,STREET_NUMBER', geometryType='NONE', loadDistinct='NO', suffix='', crs='Auto' {$(DatasetInlineTable)}
;
[DatasetInlineTable] = '';
/* End GeoAnalytics operation AddressPointLookup ----------------------------------- */

 

After edit

The header and the call is moved inside of the loop. chunkSize decides how big each split is.

/* Generated by GeoAnalytics for operation AddressPointLookup ---------------------- */
Let numRows = NoOfRows('data');
Let chunkSize = 1000;
Let chunks = numRows/chunkSize;
For n = 0 to chunks

   Let [DatasetInlineTable] = 'id' & Chr(9) & 'STREET_NAME' & Chr(9) & 'STREET_NUMBER';

   Let chunkText = '';
   Let chunk = n*chunkSize;
   For i = 0 To chunkSize-1
      Let row = '';
      Let rowNr = chunk+i;
      Exit for when rowNr >= numRows;
      For Each f In 'id', 'STREET_NAME', 'STREET_NUMBER'
         row = row & Chr(9) & Replace(Replace(Replace(Replace(Replace(Replace(Peek('$(f)', $(rowNr), 'data'), Chr(39), '\u0027'), Chr(34), '\u0022'), Chr(91), '\u005b'), Chr(47), '\u002f'), Chr(42), '\u002a'), Chr(59), '\u003b');
      Next
     chunkText = chunkText & Chr(10) & Mid('$(row)', 2);

   Next 
   [DatasetInlineTable] = [DatasetInlineTable] & chunkText;

   [AddressPointLookupResult]:
   SQL SELECT [id], [Dataset_Address], [Dataset_Geometry], [CountryIso2], [Dataset_Adm1Code], [Dataset_City], [Dataset_PostalCode], [Dataset_Street], [Dataset_HouseNumber], [Dataset_Match] 
   FROM AddressPointLookup(searchTextField='', country='"Canada"', stateField='', cityField='"Toronto"', postalCodeField='', streetField='STREET_NAME', houseNumberField='STREET_NUMBER', matchThreshold='0.5', service='default', dataset='Dataset')
   DATASOURCE Dataset INLINE tableName='data', tableFields='id,STREET_NAME,STREET_NUMBER', geometryType='NONE', loadDistinct='NO', suffix='', crs='Auto' {$(DatasetInlineTable)}
   ;
   [DatasetInlineTable] = '';

Next
chunkText=''
/* End GeoAnalytics operation AddressPointLookup ----------------------------------- */


 

Labels (2)
Contributors
Version history
Last update:
‎2021-07-01 06:07 AM
Updated by: