GeoAnalytics - close and routes question regarding multiple layers

[124rooski]

We are using GeoAnalytics connector the close geo operation. It is working absolutely perfect to see which stores are closest for our customers addresses. Now, we want to add an additional layer on our map object to also show closest airports from our customers addresses. Is this even possible to add an additional "close" geo operations on top of one already? I'd ideally like to have 2 lines from our customers home address. One line would be the the closest airport, and the other would be for the closest store (each line color coded differently). 

I just want to know if Qlik can handle these capability I want to implement. Do I need to use a close and a route connector? Can I have 2 close connectors within the same app? I cannot find these answers on the web and wanted to ask directly to all you Qlik developers. A hard answer may save time and help me find the next solution. 

[lironbaram]

hi 

you can create as much closest connections between layers as you want in your model 

but you'll have to make sure the connections between the tables doesn't create a loop 

[124rooski]

I am very glad to hear that. I am able to get one of the close routes to work perfectly. I use 2 bubble layers to show my "from" and "destination" points and then use a line layer to create the linkage between the 2 points. 

Here is my script for CUSTOMER to closest STORES.

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

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

[ClosestAssociations]:
SQL SELECT [CUSTOMER_COMPANY_STORES_RelationId], [CUSTOMER_KEY], [STORE_KEY], [CUSTOMER_COMPANY_STORES_Distance] FROM Closest(costUnit='Miles', distance='75', distanceType='bird', closestCount='1', dataset1='CUSTOMER', dataset2='COMPANY_STORES')
DATASOURCE CUSTOMER INLINE tableName='CUSTOMER_INFO', tableFields='CUSTOMER_KEY,CUSTOMER_POINT', geometryType='POINT', loadDistinct='NO', suffix='', crs='Auto' {$(CUSTOMERInlineTable)}
DATASOURCE COMPANY_STORES INLINE tableName='STORES', tableFields='STORE_KEY,STORE_POINT', geometryType='POINT', loadDistinct='NO', suffix='', crs='Auto' {$(COMPANY_STORESInlineTable)}
;
tag field [CUSTOMER_COMPANY_STORES_RelationId] with '$primarykey';

[CUSTOMERInlineTable] = '';
[COMPANY_STORESInlineTable] = '';

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

I follow the same procedure when trying to set up the same thing but for my customers and airports. Here is the script for setting the 2nd close geo operation.

LIB CONNECT TO 'GA';

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

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

[ClosestAssociations]:
SQL SELECT [CUSTOMER_AIRPORTS_RelationId], [CUSTOMER_KEY], [AIRPORTS_KEY], [CUSTOMER_AIRPORTS_Distance] FROM Closest(costUnit='Miles', distance='75', distanceType='bird', closestCount='1', dataset1='CUSTOMER', dataset2='AIRPORTS')
DATASOURCE CUSTOMER INLINE tableName='CUSTOMER_INFO', tableFields='CUSTOMER_KEY,CUSTOMER_POINT', geometryType='POINT', loadDistinct='NO', suffix='', crs='Auto' {$(CUSTOMERInlineTable)}
DATASOURCE AIRPORTS INLINE tableName='AIRPORTS_TABLE', tableFields='AIRPORTS_KEY,AIRPORTS_POINTS', geometryType='POINT', loadDistinct='NO', suffix='', crs='Auto' {$(AIRPORTSInlineTable)}
;
tag field [CUSTOMER_AIRPORTS_RelationId] with '$primarykey';

[CUSTOMERInlineTable] = '';
[AIRPORTSInlineTable] = '';

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

Once the script runs and completed, the customer to airports close geo operation does not create the field "CUSTOMER_AIRPORTS_ROUTE" which is needed to create the line between my customers address and the nearest airport. My thought as to why this may be not working is because I am coinciding my 'from' points, which in this case would be 'Customer Address', and using this for both the linkage for CUSTOMER ADDRESS to STORES & CUSTOMER ADDRESS to AIRPORTS. 

[Steven_Haught · Accepted solution]

@124rooski I have done a similar set up with drive times. What I ended up having to do is create a unique table for the second set of drive times. Before your second closest load statement, bring in your customer table again. Just the Customer_key and Customer_Points. Rename them something simple such as AirCustomer_Key and AirCustomer_Point. I also named the table something different ie AirCustomer_Info. Update all those references in the subsequent closest load and you should be good to go. 

[124rooski]

Steven, sorry for the late reply but thank you for your answer. I did the same thing on my end and works perfectly. Much appreciated.