topic Re: Qlik Rest Connector Post Method in Connectivity & Data Prep

Qlik Rest Connector Post Method

OmarBenSalem — Mon, 23 Oct 2017 08:03:57 GMT

Hi guys !

I have created this connection :

authentication schema : BASIC

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.

Re: Qlik Rest Connector Post Method

mr_novice — Mon, 23 Oct 2017 21:27:52 GMT

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

Cris

Re: Qlik Rest Connector Post Method

OmarBenSalem — Tue, 24 Oct 2017 07:49:19 GMT

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;

Re: Qlik Rest Connector Post Method

Anonymous — Fri, 03 Nov 2017 11:30:05 GMT

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

Re: Qlik Rest Connector Post Method

shraddha_g — Sat, 04 Nov 2017 03:28:45 GMT

You can implement Incremental load using script mentioned by Omar.

Re: Qlik Rest Connector Post Method

Anonymous — Mon, 06 Nov 2017 15:03:22 GMT

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

Re: Qlik Rest Connector Post Method

Anonymous — Mon, 06 Nov 2017 15:03:54 GMT

My API: http://maps.googleapis.com/maps/api/distancematrix/json?origins=Berlin&destinations=hamburg&mode=driving&language=en

Re: Qlik Rest Connector Post Method

OmarBenSalem — Mon, 06 Nov 2017 15:06:43 GMT

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

Re: Qlik Rest Connector Post Method

OmarBenSalem — Wed, 08 Nov 2017 14:41:46 GMT

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:

Hope this helps: sandeep.roy‌