Qlik Community

Qlik Sense Data Connectivity

Discussion board where members can learn more about Qlik Sense Data Connectivity.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
ahmada
New Contributor II

REST Connector Map Route API - Set Querys as Variable

Hello,
I have an API from the map provider HERE which allows me to calculate the distances between two locations. (https://developer.here.com/documentation/routing/dev_guide/topics/request-a-simple-route.html) Now I wanted to define the query for waypoint 0 and waypoint 1 as variables in the script. (see screenshot)

As source data I have a table (Destinations) with customer names and longitudes and latitudes. And the table Origin with my location as longitude and latitude.

How do I get it to loop through the Origins and Destinations? I tried with "With Connection" but it doesnst work. I hope you have understood my problem and can help me. Thank you .

 

Origins

Standortlonglat
Adlershof52.44007513.5241164

 

Destination

Namelonglat
Kunde A48.13512411.581981
Kunde B49.0068898.403653

 

let vOrigin = Origin;
let vDestination = Destination;

Origins:
LOAD
'geo!'&long&','&lat as Origin, (format for API)

Standort,
long,
lat
FROM [lib://Downloads/Origins.xlsx]
(ooxml, embedded labels, table is Tabelle1);

Destinations:
Load
'geo!'&long&','&lat as Destination, (format for API)

Name,
long as dlong,
lat as dlat
FROM [lib://Downloads/Adressen.xlsx]
(ooxml, embedded labels, table is Tabelle1);

LIB CONNECT TO 'Here';

RestConnectorMasterTable:
SQL SELECT
"__KEY_response",
(SELECT
"__FK_route",
"__KEY_route",
(SELECT
"distance",
"trafficTime",
"baseTime",
"text",
"travelTime" AS "travelTime_u1",
"_type" AS "_type_u0",
"__KEY_summary",
"__FK_summary",
(SELECT
"@Value" AS "@Value_u2",
"__FK_flags"
FROM "flags" FK "__FK_flags" ArrayValueAlias "@Value_u2")
FROM "summary" PK "__KEY_summary" FK "__FK_summary")
FROM "route" PK "__KEY_route" FK "__FK_route")
FROM JSON (wrap off) "response" PK "__KEY_response"
WITH CONNECTION(
QUERY "waypoint0" "$(Origin)",
QUERY "waypoint1" "$(vDestination)"
);

[flags]:
LOAD [@Value_u2],
[__FK_flags] AS [__KEY_summary]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_flags]);


[summary]:
LOAD [distance],
[trafficTime],
[baseTime],
[text],
[travelTime_u1] AS [travelTime_u1],
[_type_u0] AS [_type_u0],
[__KEY_summary],
[__FK_summary] AS [__KEY_route]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_summary]);


DROP TABLE RestConnectorMasterTable;

Error.png