Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Does anyone have working example of such an implementation?
I can use variable withing URL but not as query header.
In below example:
let v='Assignments';
let v2='Authorization%2WRAP access_token%%2%%3client%%2336b434e68de4f97ba5ad160.26395&user_token%%2db2b40ac154c4dbeb36f5e686899b211.26395%%3%1Accept%2application/json';
CUSTOM CONNECT TO "Provider=QvRestConnector.exe;url= https://api.dovico.com/$(v)/?version=5;
timeout=30;method=GET;autoDetectResponseType=true;
keyGenerationStrategy=3;useWindowsAuthentication=false;useCertificate=No;
certificateStoreLocation=CurrentUser;certificateStoreName=My;queryParameters=version%25;
queryHeaders=$(v2);
PaginationType=None;
XUserId=BIZYJNJbaCGLA;
XPassword=cJfbZSUEQLYGXZdOPTcIWMFTcSEXVKLLJCTaB;";
the first variable v works but the second v2 throws an error:
Parameter "$(v2)" has no value, or a misplaced semicolon.
CUSTOM CONNECT TO "Provider=QvRestConnector.exe;url= https://api.dovico.com/Assignments/?version=5;
timeout=30;method=GET;autoDetectResponseType=true;
keyGenerationStrategy=3;useWindowsAuthentication=false;useCertificate=No;
certificateStoreLocation=CurrentUser;certificateStoreName=My;queryParameters=version%25;
queryHeaders=$(v2);
PaginationType=None;
XUserId=BIZYJZJbaCGLA;
XPassword=cJfbZSUEQLYGXZdOPTcDCJMFTcSEXVKLLJCTaB;"
Petter:
I was able to follow your logic with help from Qlik's Architect, but now I have another issue:
The loop goes through all states, but the output is only one state:01 that is specified in the connection string
1. Connection:
'ABC'
2. Script:
for i= 01 TO 56
paramURL = 'https://api.census.gov/data/2015/acs5?get%2NAME,GEOID&for%2school+district+(unified):*&in%2state:$(i...mykeyxxx;timeout=30;method=GET;autoDetectResponseType=true;keyGenerationStrategy=0;useWindowsAuthentication=false;useCertificate=No;certificateStoreLocation=CurrentUser;certificateStoreName=My;PaginationType=None;XUserId=xxx;XPassword=xxx;';
LIB CONNECT TO 'ABC';
RestConnectorMasterTable:
SQL SELECT
"__KEY_root",
(SELECT
"@Value",
"__FK_root"
FROM "root" FK "__FK_root" ArrayValueAlias "@Value")
FROM JSON (wrap on) "root" PK "__KEY_root"
CUSTOM CONNECT TO "Provider=QvRestConnector.exe;url=$(paramURL)"
WITH CONNECTION (
URL "$(paramURL)"
);
[root]:
LOAD [@Value] AS [@Value],
[__FK_root] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_root]);
next
DROP TABLE RestConnectorMasterTable;
Petter:
I have figured out what was the issue. I was mixing CUSTOM CONNECT TO with WITH CONNECTION
Working Script:
LIB CONNECT TO 'ABC';
let paramURL = 'https://api.census.gov/data/2015/acs5?get=NAME,GEOID&for=school+district+(unified):*&in=state:$(i)&k...
RestConnectorMasterTable:
SQL SELECT
"__KEY_root",
(SELECT
"@Value",
"__FK_root"
FROM "root" FK "__FK_root" ArrayValueAlias "@Value")
FROM JSON (wrap on) "root" PK "__KEY_root"
WITH CONNECTION (
URL "$(paramURL)"
);
[root]:
LOAD
[@Value] AS [@Value],
[__FK_root] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_root]);
DROP TABLE RestConnectorMasterTable;
next
Hi,
It it possible to store the Authorization token inside a variable that. I need to run the API inside a loop
and on each pass update the authorization token..
eg
CUSTOM CONNECT TO "Provider=QvRestConnector.exe;url=https://.mydomain/;timeout=30;method=GET;autoDetectResponseType=true;keyGenerationStrategy=0;useWind... 'headers'%1Authorization%2Token token%%$(APIToken);PaginationType=None;XUserId=xxx;XPassword=99999;";
Is this possible?
Many thanks for any advice!
Phil
Hi Petter,,
The API i have been told to use will only bring back 100 records at a time. I have been told to use 'Limit' and 'Offset' in my query parameters - nut cannot get this to work. Do I need any more information from the developers for this?
Thanks for any advice,
Phil
Hi ,
I am looking for the solution to filter the data from rest connector.
I have a query like below
SQL SELECT
"id" as "id",
"name" as "name_u1"
FROM JSON (wrap on) "root" PK "__PK_root";
From name field I have to filter only Approved Data i.e., name= 'Approved'.
Could you please help me to write the condition on API. I have tried query Parameters but no luck.
Thanks in advance.