Qlik Community

Connectivity & Data Prep

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

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
OmarBenSalem
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
OmarBenSalem
Partner
Partner
Author

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
mr_novice
Creator II
Creator II

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

OmarBenSalem
Partner
Partner
Author

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
Creator II
Creator II

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??

shraddha_g
Partner
Partner

You can implement Incremental load using script mentioned by Omar.

sandeep_roy
Creator II
Creator II

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..

OmarBenSalem
Partner
Partner
Author

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

OmarBenSalem
Partner
Partner
Author

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