Qlik Community

Qlik GeoAnalytics Discussions

Discussion Board for collaboration regarding Qlik GeoAnalytics.

Highlighted
Partner
Partner

Qlik GeoAnalystics Plus, issue with generated script using closest operator for routing data

Below script is throwing a timeout error while reaching to the Route Module:

LIB CONNECT TO 'IAG GeoAnalytics Connector (XYZ)';

/* Generated by GeoAnalytics for operation Closest ---------------------- */
[_inlineMap_]:
mapping LOAD * inline [
_char_, _utf_
"'", '\u0027'
'"', '\u0022'
"[", '\u005b'
"/", '\u002f'
"*", '\u002a'
";", '\u003b'
"}", '\u007d'
"{", '\u007b'
"`", '\u0060'
"´", '\u00b4'
" ", '\u0009'
];

IF FieldNumber('IAGSiteId', 'IAGSite') = 0 THEN
call InvalidInlineData('The field IAGSiteId in IAGSite is not available');
END IF
IF FieldNumber('IAGLatLong', 'IAGSite') = 0 THEN
call InvalidInlineData('The field IAGLatLong in IAGSite is not available');
END IF
Let [IAGInlineTable] = 'IAGSiteId' & Chr(9) & 'IAGLatLong';
Let numRows = NoOfRows('IAGSite');
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 'IAGSiteId', 'IAGLatLong'
row = row & Chr(9) & MapSubString('_inlineMap_', Peek('$(f)', $(rowNr), 'IAGSite'));
Next
chunkText = chunkText & Chr(10) & Mid('$(row)', 2);
Next
[IAGInlineTable] = [IAGInlineTable] & chunkText;
Next
chunkText=''
[_inlineMap_]:
mapping LOAD * inline [
_char_, _utf_
"'", '\u0027'
'"', '\u0022'
"[", '\u005b'
"/", '\u002f'
"*", '\u002a'
";", '\u003b'
"}", '\u007d'
"{", '\u007b'
"`", '\u0060'
"´", '\u00b4'
" ", '\u0009'
];

IF FieldNumber('LogisticsPointId', 'LogisticsPoint') = 0 THEN
call InvalidInlineData('The field LogisticsPointId in LogisticsPoint is not available');
END IF
IF FieldNumber('LogisticsPointLatLong', 'LogisticsPoint') = 0 THEN
call InvalidInlineData('The field LogisticsPointLatLong in LogisticsPoint is not available');
END IF
Let [LogisticsInlineTable] = 'LogisticsPointId' & Chr(9) & 'LogisticsPointLatLong';
Let numRows = NoOfRows('LogisticsPoint');
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 'LogisticsPointId', 'LogisticsPointLatLong'
row = row & Chr(9) & MapSubString('_inlineMap_', Peek('$(f)', $(rowNr), 'LogisticsPoint'));
Next
chunkText = chunkText & Chr(10) & Mid('$(row)', 2);
Next
[LogisticsInlineTable] = [LogisticsInlineTable] & chunkText;
Next
chunkText=''


[ClosestAssociations]:
SQL SELECT [IAG_Logistics_RelationId], [IAGSiteId], [LogisticsPointId], [IAG_Logistics_Distance] FROM Closest(costUnit='Miles', distance='30', distanceType='bird', closestCount='1', dataset1='IAG', dataset2='Logistics')
DATASOURCE IAG INLINE tableName='IAGSite', tableFields='IAGSiteId,IAGLatLong', geometryType='POINT', loadDistinct='NO', suffix='', crs='Auto' {$(IAGInlineTable)}
DATASOURCE Logistics INLINE tableName='LogisticsPoint', tableFields='LogisticsPointId,LogisticsPointLatLong', geometryType='POINT', loadDistinct='NO', suffix='', crs='Auto' {$(LogisticsInlineTable)}
;
tag field [IAG_Logistics_RelationId] with '$primarykey';

[IAGInlineTable] = '';
[LogisticsInlineTable] = '';

/* End GeoAnalytics operation Closest ----------------------------------- */

Tags (1)
1 Reply
Employee
Employee

Re: Qlik GeoAnalystics Plus, issue with generated script using closest operator for routing data

Hi,
I recommend splitting up large tables for better performance and less risk of timeouts:
https://community.qlik.com/t5/Qlik-GeoAnalytics-Documents/Splitting-up-GeoAnalytics-connector-operat...

Thanks,
Patric