Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi All,
how to find the distance between two pin codes using geo Analytics .I tried but didn't work can some one can help me here ?
Here is my code :
rutt:
LOAD
routeid,
// TicketID,
// "Start",
// Start_Country,
// "end",
// End_Country,
Startpincode,
Endpincode
FROM [lib://AttachedFiles/GeoSampledata_Pincode.xlsx]
(ooxml, embedded labels, table is rutt);
//==================Geo Coding===============================
LIB CONNECT TO 'Geo Connection';
/* Generated by GeoAnalytics for operation Routes ---------------------- */
[_inlineMap_]:
mapping LOAD * inline [
_char_, _utf_
"'", '\u0027'
'"', '\u0022'
"[", '\u005b'
"/", '\u002f'
"*", '\u002a'
";", '\u003b'
"}", '\u007d'
"{", '\u007b'
"`", '\u0060'
"´", '\u00b4'
" ", '\u0009'
];
IF FieldNumber('routeid', 'rutt') = 0 THEN
call InvalidInlineData('The field routeid in rutt is not available');
END IF
IF FieldNumber('Startpincode', 'rutt') = 0 THEN
call InvalidInlineData('The field Startpincode in rutt is not available');
END IF
IF FieldNumber('Endpincode', 'rutt') = 0 THEN
call InvalidInlineData('The field Endpincode in rutt is not available');
END IF
Let [routeInlineTable] = 'routeid' & Chr(9) & 'Startpincode' & Chr(9) & 'Endpincode';
Let numRows = NoOfRows('rutt');
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 'routeid', 'Startpincode', 'Endpincode'
row = row & Chr(9) & MapSubString('_inlineMap_', Peek('$(f)', $(rowNr), 'rutt'));
Next
chunkText = chunkText & Chr(10) & Mid('$(row)', 2);
Next
[routeInlineTable] = [routeInlineTable] & chunkText;
Next
chunkText=''
[Routes]:
SQL SELECT [routeid], [route_Distance], [route_Duration], [route_Status], [route_PathLowRes] FROM Routes(criteria='fastest', transportation='car', dest='EndPincode', destType='NamedPoint', suffix='XPC', dataset='route')
DATASOURCE route INLINE tableName='rutt', tableFields='routeid,Startpincode,Endpincode', geometryType='LOCATIONNAMEDPOINT', loadDistinct='NO', suffix='XPC', crs='Auto' {$(routeInlineTable)}
SELECT [routeid], [Startpincode], [Endpincode] FROM route;
[route]:
SQL LOAD * FROM route;
tag field [routeid] with '$primarykey';
tag field [route_PathLowRes] with '$geoline';
tag field [routeid] with '$geoname';
tag field [route_PathLowRes] with '$relates_routeid';
tag field [routeid] with '$relates_route_PathLowRes';
tag field [routeid] with '$primarykey';
tag field [Startpincode] with '$geopoint';
tag field [routeid] with '$geoname';
tag field [Startpincode] with '$relates_routeid';
tag field [routeid] with '$relates_Startpincode';
[routeInlineTable] = '';
/* End GeoAnalytics operation Routes ----------------------------------- */
Note: i can able to find the distance between two cities but not using pin codes .I am Using Qliksense nov2019 Release
Please find the sample data below
Check out the "Routes" example here: https://community.qlik.com/t5/Qlik-GeoAnalytics-Documents/Connector-Examples-Qlik-GeoAnalytics/ta-p/...
Test with a point layer if the zip codes can be looked up.
How to improve the lookup accuracy - Qlik Community
Then in the operation, add country code as a suffix:
,IN
Thanks,
Patric
Thanks for your reply .
when i am trying to use the geometry type as Point i am getting below error . please find the attached files and suggest me if i am doing something wrong.
My Script :
LIB CONNECT TO 'Geo Connection';
/* Generated by GeoAnalytics for operation Routes ---------------------- */
[_inlineMap_]:
mapping LOAD * inline [
_char_, _utf_
"'", '\u0027'
'"', '\u0022'
"[", '\u005b'
"/", '\u002f'
"*", '\u002a'
";", '\u003b'
"}", '\u007d'
"{", '\u007b'
"`", '\u0060'
"´", '\u00b4'
" ", '\u0009'
];
IF FieldNumber('routeid', 'rutt') = 0 THEN
call InvalidInlineData('The field routeid in rutt is not available');
END IF
IF FieldNumber('Startpincode', 'rutt') = 0 THEN
call InvalidInlineData('The field Startpincode in rutt is not available');
END IF
IF FieldNumber('Endpincode', 'rutt') = 0 THEN
call InvalidInlineData('The field Endpincode in rutt is not available');
END IF
Let [routeInlineTable] = 'routeid' & Chr(9) & 'Startpincode' & Chr(9) & 'Endpincode';
Let numRows = NoOfRows('rutt');
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 'routeid', 'Startpincode', 'Endpincode'
row = row & Chr(9) & MapSubString('_inlineMap_', Peek('$(f)', $(rowNr), 'rutt'));
Next
chunkText = chunkText & Chr(10) & Mid('$(row)', 2);
Next
[routeInlineTable] = [routeInlineTable] & chunkText;
Next
chunkText=''
[Routes]:
SQL SELECT [routeid], [route_Distance], [route_Duration], [route_Status], [route_PathLowRes] FROM Routes(criteria='fastest', transportation='car', dest='EndPincode', destType='NamedPoint', suffix='', dataset='route')
DATASOURCE route INLINE tableName='rutt', tableFields='routeid,Startpincode,Endpincode', geometryType='POINT', loadDistinct='NO', suffix='', crs='Auto' {$(routeInlineTable)}
SELECT [routeid], [route_Geometry] FROM route;
[route]:
SQL LOAD * FROM route;
tag field [routeid] with '$primarykey';
tag field [route_PathLowRes] with '$geoline';
tag field [routeid] with '$geoname';
tag field [route_PathLowRes] with '$relates_routeid';
tag field [routeid] with '$relates_route_PathLowRes';
tag field [routeid] with '$primarykey';
tag field [route_Geometry] with '$geopoint';
tag field [routeid] with '$geoname';
tag field [route_Geometry] with '$relates_routeid';
tag field [routeid] with '$relates_route_Geometry';
[routeInlineTable] = '';
/* End GeoAnalytics operation Routes ----------------------------------- */
Thanks
hi ,
i am getting the distance between two cities but i can able to pull only 350 records .when i am trying to pull all the records i am getting below error message .
Error message :Size of data set routes is higher than the maximum size allowed .
please help me with the best solution here.
Note: error message attached
Thanks,
Abubakar Siddiq
The cloud service has limits, split up the calculations in to several calls.
Check here how> https://community.qlik.com/t5/Qlik-GeoAnalytics-Documents/Splitting-up-GeoAnalytics-connector-operat...
Thanks,
Patric