Retrieving data from source in Json format

Koen_D — Fri, 11 Nov 2022 15:03:11 GMT

Hello Qlik experts,

I've been struggling to get Qlik Sense to communicate with a database (Exact Online) that uses OAuth2. I've managed to get the token aspect to work, however now I'm stuck on how to retrieve the data from the database.

What I've done:

1. Set up an authorization process and a POST call in Qlik to get my access and refresh tokens.

2. Automatically get new access token if needed.

I'm now able to connect to the database, but I'm failing to retrieve any data.

An example end point is as follows:

https://start.exactonline.nl/api/v1/current/Me?$select=CurrentDivision

Which in postman gives me this result (changed the division):

{

"d": {

"results": [

{

"__metadata": {

"uri": "https://start.exactonline.nl/api/v1/current/Me(guid'1abcd')",

"type": "Exact.Web.Api.System.Me"

"CurrentDivision": 1

}

]

}

If I can get to extract CurrentDivision then I'll be able to figure the rest out.

In Qlik I have to following:

LIB CONNECT TO 'REST_GET'; // Dummy connection

set vAccept = 'application/json';
set vRequestBody = '';
set vURL = 'https://start.exactonline.nl/api/v1/current/Me?$select=CurrentDivision';

MasterTable:
SQL SELECT
"__KEY_root",
(SELECT "CurrentDivision",
"__FK_results"
FROM "results" FK "__FK_results")
FROM JSON (wrap on) "root" PK "__KEY_root"
WITH CONNECTION (
URL "$(vURL)",
HTTPHEADER "Authorization" "Bearer $(vAccessToken)",
HTTPHEADER "Accept" "$(vAccept)",
BODY "$(vRequestBody)"
);

I've also tried:

MasterTable:
SQL SELECT "d" as "d"
FROM JSON (wrap on) "root"
WITH CONNECTION (
URL "$(vURL)",
HTTPHEADER "Authorization" "Bearer $(vAccessToken)",
HTTPHEADER "Accept" "$(vAccept)",
BODY "$(vRequestBody)"
);

Both of which just give me 0 lines of data in return, or a 1 for __Key_Root. No error, just no data.

Any ideas?

topic Retrieving data from source in Json format in Integration, Extension & APIs

Retrieving data from source in Json format