Qlik Community

Ask a Question

Connectivity & Data Prep

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

Announcements
Our May 2021 end-to-end product release from Data Integration to Data Analytics is out! READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
christianflood
Partner
Partner

Include field value from one rest connection to query parameter in 2nd rest connection

Hello,

I have a question regarding rest connections;

I would like a field in one rest connector to be used in 2nd rest connectors query parameter which changes dynamically. The reason is that the rest connector needs an value for a certain parameter as input.

The parameter can be found from another rest connector, which has the format parameter=01234, where 01234 should be passed as a parameter in the second rest connector.

However I have tried placing the "let vActid2=ActId2" in various places but it don't seem to work wherever. I guess I don't actually know how the data loading works fully.

Im using the WITH CONNECTION approach in the 2nd connector, but I can't get i to work.

Ex. (wrote over certain sensitive data)

LIB CONNECT TO 'rest connector 1 (admin)';

RestConnectorMasterTable:

SQL SELECT

"TypeName",

"EndTime",

"StartTime",

"Name",

"Id",

"__KEY_root"

FROM JSON (wrap on) "root" PK "__KEY_root";

[root]:

LOAD [TypeName] AS [TypeName],

[EndTime] AS [EndTime],

[StartTime] AS [StartTime],

[Name] AS [Name],

[Id] AS [ActId], //ActId has format act=12354 where 12345 should be passed to second rest connector

if(now() > [StartTime] and now() < [EndTime] and [TypeName]='Type1', subfield([Id],'=',2)) as ActId2

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__KEY_root]);

let vActId2 = ActId2;

DROP TABLE RestConnectorMasterTable;

   

LIB CONNECT TO 'rest connector 2 (admin)';

RestConnectorMasterTable:

SQL SELECT

"__KEY_root",

(SELECT

"_id",

"userId",

"__FK_UserKey"

FROM "UserKey" FK "__FK_UserKey"),

(SELECT

"srsYPos",

"srsXPos",

"seqNo",

"posDate",

"__FK_MapPosList"

FROM "MapPosList" FK "__FK_MapPosList")

FROM JSON (wrap on) "root" PK "__KEY_root"

WITH CONNECTION (

QUERY "aktivityId" "$(vActId2)");

[UserKey]:

LOAD [_id] AS [_id],

[userId] AS [userId],

[__FK_UserKey] AS [NewKEY_root]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_UserKey]);

[MapPosList]:

LOAD [srsYPos] AS [utsrsYPos],

[srsXPos] AS [utsrsXPos],

GeoMakePoint([srsYPos],[srsXPos]) as UserTracking,

GeoMakePoint(previous([srsYPos]),previous([srsXPos])) as UserTrackingFrom,

[seqNo] AS [seqNo],

[posDate] AS [posDate],

    ConvertToLocalTime(TimeStamp(MakeDate(1970) + ([posDate]/24/60/60/1000))) as PositionDate,

    WeekDay(ConvertToLocalTime(TimeStamp(MakeDate(1970) + ([posDate]/24/60/60/1000)))) as WeekDay,

week(ConvertToLocalTime(TimeStamp(MakeDate(1970) + ([posDate]/24/60/60/1000)))) as Week, 

    If(num(ConvertToLocalTime(TimeStamp(MakeDate(1970) + ([posDate]/24/60/60/1000))))>=(num(Today()-0.333333333333333)),1,0) as DayFilterFlag,

    If(num(ConvertToLocalTime(TimeStamp(MakeDate(1970) + ([posDate]/24/60/60/1000))))>=(num(Today()-7.333333333333333)),1,0) as WeekFilterFlag,

[__FK_MapPosList] AS [NewKEY_root]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_MapPosList]);

[root]:

LOAD [__KEY_root] AS [NewKEY_root]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__KEY_root]);

DROP TABLE RestConnectorMasterTable;

1 Reply
Gysbert_Wassenaar

Change: let vActId2 = ActId2;

To: let vActId2 = Peek('ActId2',-1,'RestConnectorMasterTable');


talk is cheap, supply exceeds demand