topic Re: Rest Connector with variable as query parameter in Connectivity & Data Prep

Rest Connector with variable as query parameter

mr_novice — Mon, 23 Oct 2017 14:20:31 GMT

Hi All!

Im working with the REST-connector. Which works fine! But, I need to change one of the query parameters every day. I need to send current date every time in the URL as a parameter and want to create a variable with this date. How can I accomplish this?

Cris

Re: Rest Connector with variable as query parameter

mr_novice — Mon, 23 Oct 2017 20:16:24 GMT

Hi,

I created this script but it throws me an error "There is no open data connection. The generated URL is correct and I can copy it from the error message and paste it in the browser and it works. Any ideas what to do? How can I open the connection?

Im using QlikSense Business Cloud.

LET vStartDate = date(addmonths(Today(),-6),'D.M.YYYY');
LET vEndDate = date(Today(),'D.M.YYYY');

Let vURL = 'https://X/NordicSharesEOD/EODPricesDD?';
LET vURL = vURL & '&token=GUSER-1f13bf6d-516e-4d0f-96fe-93011d77c791';
LET vURL = vURL & '&todate=' & '$(vEndDate)';
LET vURL = vURL & '&fromdate=' &  '$(vStartDate)';
LET vURL = vURL & '&Accept=text/json';


[DailySymbols]:
SQL SELECT
"symbol",
"trading_date",
"official_last",
"paid_first",
"paid_high",
"paid_low",
"volume_traded",
"exchangename"
FROM JSON (wrap on) "root" QDL
WITH CONNECTION (
URL "$(vURL)"
)
;

Any help or suggestion is appreciated.

Cris

Re: Rest Connector with variable as query parameter

mr_novice — Mon, 23 Oct 2017 21:47:32 GMT

This works fine. I created a dummy REST-connection to "open a connection". Then its modified by the "with connection".

LIB CONNECT TO 'DummyGetConnection';

LET vStartDate = date(addmonths(Today(),-6),'D.M.YYYY');
LET vEndDate = date(Today(),'D.M.YYYY');

Let vURL = 'https://X/NordicSharesEOD/EODPricesDD?';
LET vURL = vURL & '&token=GUSER-31fbe38f-43d9-427f-8dfa-b6e82570';
LET vURL = vURL & '&todate=' & '$(vEndDate)';
LET vURL = vURL & '&fromdate=' &  '$(vStartDate)';
LET vURL = vURL & '&Accept=text/json';

[DailySymbols]:
SQL SELECT 
"symbol",
"trading_date",
"official_last",
"paid_first",
"paid_high",
"paid_low",
"volume_traded",
"exchangename"
FROM JSON (wrap on) "root" QDL
WITH CONNECTION (
URL "$(vURL)"
)
;

Re: Rest Connector with variable as query parameter

nategreen — Mon, 25 Mar 2019 15:06:07 GMT

I am having a similar issue wit a Rest connection where i need to have a dynamic date in the query parameter (yesterday). When i run the connection in legacy mode with the date as a QS variable, i get an authorization error. However, when i use the QS data connection with authorization, i can't figure out how to pass the QS variable to the data connection's query parameter. How are you retrieving the "&token=GUSER-31fbe38f-43d9-427f-8dfa-b6e82570" from the data connection in the above example? do you have a recommendation of how to execute my problem?

thx!

Nate