Helpful Qlik script snippets in combination with REST connector. For a demo visit https://youtu.be/7m9ZejlzkkY. The attached Sense App QVF contains all the below script snippets.
Put your API base URL into a variable at the beginning of your script to be used in all the REST calls.
LET vBaseAPIurl = 'https://blablabla.foo.com/api/v1';
The WITH CONNECTION block has the magic to make a REST call dynamic and to pass variables defined before. After you inserted your LOAD statement with the "Select Data" wizard, search for the end of the SQL SELECT block - it ends with FROM JSON (...); - and copy/paste this:
WITH CONNECTION (
URL "$(vBaseAPIurl)/endpoint/method"
,QUERY "pagesize" "1000"
//,HTTPHEADER "Cache-Control" "no-cache"
,HTTPHEADER "Authorization" "Bearer $(vToken)"
,HTTPHEADER "Content-Type" "application/json"
//,HTTPHEADER "X-HTTP-Method-Override" "PUT",
,BODY "{""key"":""value""}"
//,BODY "$(vJsonBody)"
);
I know, it has nothing to do with SQL, and I don't know why the developers of the REST connector didn't pick another keyword for Qlik Script to begin a LOAD block :-) ... think of SQL SELECT as API SELECT ...
The easiest way to provide Json in the API call is to put the Json block first into a script variable and then to clean and format the variable. APIs typically expect strict notation, that means quotes around keys and values) e.g. {"key":"value"}
// Define your Json-Body here in a variable
SET vJsonBody = {
"dateFrom": "2018-01-01T00:00:00.000Z",
"dateTo": "$(vDateTo)"
};
// replace 1xquote with 2xquote and remove line-break and tabulator chars
LET vJsonBody = PurgeChar(Replace(vJsonBody,'"','""'),CHR(13)&CHR(10)&CHR(8));
Enjoy.