REST Connector Map Route API - Set Querys as Variable

[ahmada]

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

Standort	long	lat
Adlershof	52.440075	13.5241164

Destination

Name	long	lat
Kunde A	48.135124	11.581981
Kunde B	49.006889	8.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;