Qlik Community

Qlik Sense Data Connectivity

Discussion board where members can learn more about Qlik Sense Data Connectivity.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Partner
Partner

Qlik Rest Connector Post Method

Hi guys !

I have created this connection :

Capture.PNG

authentication schema : BASIC

Capture.PNG

When I test the connection, the alertName sepcified in the request body is Posted !

Now, the question is, how to translate this into the script?

So that I can change the BodayRequest dynamically?

What I want to do is bascially do this :

Store a QlikSense table directly into an SQL table using the script.

but using the Qlik REST Connector (the post Method) to directly interact with the WS.

Your help is much appreciated !

Omar BEN SALEM.

1 Solution

Accepted Solutions
Partner
Partner

Re: Qlik Rest Connector Post Method

Yes indeed, this is how I proceeded:

LET vRequestBody ='{';

Let vRequestBody = vRequestBody&'"alertName":"$(vAlert)",';

Let vRequestBody = vRequestBody&'"utcodeComment":"$(vDesk)",';

Let vRequestBody = vRequestBody&'"comment":"$(vDesk)"';

Let vRequestBody = vRequestBody&'}';

let vRequestBody = replace(vRequestBody,'"', chr(34)&chr(34));

LIB CONNECT TO 'Post Alert';

RestConnectorMasterTable:

SQL SELECT

"alertName",

"utcodeComment",

"id",

"comment",

"dateComment"

FROM JSON (wrap on) "root" WITH CONNECTION(

BODY "$(vRequestBody)"

);;

[root]:

LOAD [alertName] AS [alertName],

[utcodeComment] AS [utcodeComment],

[id] AS [id],

[comment] AS [comment],

[dateComment] AS [dateComment]

RESIDENT RestConnectorMasterTable;

DROP TABLE RestConnectorMasterTable;

View solution in original post

8 Replies
Highlighted
mr_novice
Contributor II

Re: Qlik Rest Connector Post Method

Hi Omar,

I think you should create a "dummy" POST connection. Then when using it you modify it with your new BODY. Read more here: Re: REST CONNECTOR locate the Authorization token

Br

Cris

Partner
Partner

Re: Qlik Rest Connector Post Method

Yes indeed, this is how I proceeded:

LET vRequestBody ='{';

Let vRequestBody = vRequestBody&'"alertName":"$(vAlert)",';

Let vRequestBody = vRequestBody&'"utcodeComment":"$(vDesk)",';

Let vRequestBody = vRequestBody&'"comment":"$(vDesk)"';

Let vRequestBody = vRequestBody&'}';

let vRequestBody = replace(vRequestBody,'"', chr(34)&chr(34));

LIB CONNECT TO 'Post Alert';

RestConnectorMasterTable:

SQL SELECT

"alertName",

"utcodeComment",

"id",

"comment",

"dateComment"

FROM JSON (wrap on) "root" WITH CONNECTION(

BODY "$(vRequestBody)"

);;

[root]:

LOAD [alertName] AS [alertName],

[utcodeComment] AS [utcodeComment],

[id] AS [id],

[comment] AS [comment],

[dateComment] AS [dateComment]

RESIDENT RestConnectorMasterTable;

DROP TABLE RestConnectorMasterTable;

View solution in original post

sandeep_roy
Contributor II

Re: Qlik Rest Connector Post Method

The solution is fine. But what next if the row count is huge like 1 million count.

Has anyone tried using placing multiple API request  from the table data using REST connector??

Partner
Partner

Re: Qlik Rest Connector Post Method

You can implement Incremental load using script mentioned by Omar.

sandeep_roy
Contributor II

Re: Qlik Rest Connector Post Method

Thank you Shraddha...I have implemented long back incremental load.. But any other way I was looking to reduce the reload time.

I am using this API to calculate the distance and duration using origin and destination from my excel sheet.

what format shall I give in the request body in POST method..

sandeep_roy
Contributor II

Re: Qlik Rest Connector Post Method

Partner
Partner

Re: Qlik Rest Connector Post Method

Here's what I did:

//this the part where we use the rest connector to send the table alert to the DB

for a=0 to FieldValueCount('row')-1

Let vAlert = Peek('alert',$(a), 'alerts');

//Let vDesk = Peek('DESK2',$(a),'alerts');

Let vDateCreation = Peek('DateAlertes',$(a),'alerts');

LET vRequestBody ='{';

Let vRequestBody = vRequestBody&'"alertName":"$(vAlert)",';

Let vRequestBody = vRequestBody&'"dateCreation":"$(vDateCreation)"';

Let vRequestBody = vRequestBody&'}';

let vRequestBody = replace(vRequestBody,'"', chr(34)&chr(34));

LIB CONNECT TO 'Post Alert';

RestConnectorMasterTable:

SQL SELECT

"alertName",

"dateCreation",

"dateValidation",

"utcodeValidator"

FROM JSON (wrap on) "root" WITH CONNECTION(

BODY "$(vRequestBody)"

);

[root]:

LOAD [alertName] AS [alertName],

[dateCreation] AS [dateCreation],

[dateValidation] AS [dateValidation],

[utcodeValidator] AS [utcodeValidator]

RESIDENT RestConnectorMasterTable;

DROP TABLE RestConnectorMasterTable;

next a

Partner
Partner

Re: Qlik Rest Connector Post Method

with this :

Let vRequestBody ='[';

for a=0 to FieldValueCount('row')-1

Let vRequestBody = vRequestBody& '{"alertName" :"'& vAlert & Peek('alert',$(a), 'alerts')&'"'&',' & '"dateCreation":'&'"'& vDateCreation & Peek('DateAlertes',$(a), 'alerts')&'"'&'},';

next a

Let vRequestBody = left(vRequestBody,len(vRequestBody)-1);

Let vRequestBody=vRequestBody& ']';

LIB CONNECT TO 'Post Alert';

RestConnectorMasterTable:

SQL SELECT

"alertName",

"dateCreation",

"dateValidation",

"utcodeValidator"

FROM JSON (wrap on) "root" WITH CONNECTION(

BODY "$(vRequestBody)"

);

[root]:

LOAD [alertName] AS [alertName],

[dateCreation] AS [dateCreation],

[dateValidation] AS [dateValidation],

[utcodeValidator] AS [utcodeValidator]

RESIDENT RestConnectorMasterTable;

DROP TABLE RestConnectorMasterTable;

I have this as a requestBody:

Capture.PNG

Hope this helps: sandeep.roy