Retrieving data from data source that provides data in Json format

[Koen_D]

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?

[blaise · Accepted solution]

its quite hard to write the SQL SELECT from a rest source so what i usually do when working with API and even more with OAuth2 is that i first setup one connection to the token/auth endpoint to retrieve the token, copy the token and create a new connection where I add the token to the header parameter and url as the endpoint i would like to get data from. Then i use the add data for this connection to build up the SQL SELECT statement. Once that one is working ill switch to using the token connection and WITH CONNECTION statement.

Then i do this for all endpoints, first modify an existing one where the token is added as header parameter and then switch to WITH CONNECTION. You might need to retrieve a fresh token once in a while, depends on the token lifetime.

[Koen_D]

Could you explain what you mean with " Then i use the add data for this connection to build up the SQL SELECT statement." ? I have Oauth2 working, so I have an access token that I pass to the connection as vAccessToken in the header. 

The data I retrieve in Postman looks like the following:

{

"d": {

"results": [

{

"__metadata": {

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

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

},

"CurrentDivision": 1

}

]

}

}

Shouln't I be able to deduce the SQL statement with this knowledge?

[Koen_D]

Actually got it to work using the built in SQL statement builder using the select data feature. Thanks a lot!