Transfer Data from Qlik Sense to WEB SERVICE using Qlik REST Connector

[OmarBenSalem]

Hi community,

I assume we all, once in a while, faced this question : "Is it possible to send a table from QlikSense to our WebService/database?"

So, without further due, the answer is : YES !

I'll now share with you how I did to implement this:

0) prerequisites : The WEB SERVICE is already created.. (obviously)

1) This is a Qlik Sense Table

table:

//This is the alert table, in which will be sent to the WS

load alert,RowNo() as row, timestamp(date#(date,'DDMMYYYY'),'YYYY-MM-DD hh:mm:ss.ff') as date;

load * inline [

alert, date

alert1,06/12/2017

alert2,06/12/2017

];

PS : The row field is created to browse all the alerts to send.

2) Make a Rest Connector with your Web Service (POST method):

3) Import the data within the WS:

Result:

LIB CONNECT TO 'Post Alert';

RestConnectorMasterTable:

SQL SELECT

"alertName",

"dateCreation",

"dateValidation",

"utcodeValidator"

FROM JSON (wrap on) "root";

[root]:

LOAD [alertName] AS [alertName],

[dateCreation] AS [dateCreation],

[dateValidation] AS [dateValidation],

[utcodeValidator] AS [utcodeValidator]

RESIDENT RestConnectorMasterTable;

DROP TABLE RestConnectorMasterTable;

4) The 4th and most important step, is to modify the script above, to control the request sent to the WS.

For that, it's mandatory to add our Request Body

a)

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;

b) Let's now define the request body.

Depends on the WS (how It's constructed) ; This phase is handled by experts, as Qlikers, we just ask the question to know what to do..)

FIRST CASE: send the alerts one by one :

For this case: the request body for our Web Service should be as follow:

send :
{"alertName": "Alert1", "dateCreation": "2017-12-06 14:59:25.00"}

then

{"alertName": "Alert2", "dateCreation": "2017-12-06 14:59:26.00"}

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

//each time we peek and alert and its date and store them in variables

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

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

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

//here we write our body request using the created variables and send them (one by one)

//This is the Json Request to post in the Web Service

LET vRequestBody ='{';

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

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

Let vRequestBody = vRequestBody&'}';

let vRequestBody = replace(vRequestBody,'"', chr(34)&chr(34)); //this is mandatory

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

SECOND CASE: send all the alerts in one shot :

For this case: the request body for our Web Service should be as follow:

send:
{{"alertName": "Alert1", "dateCreation": "2017-12-06 14:59:25.00"},{"alertName": "Alert2", "dateCreation": "2017-12-06 14:59:26.00"}}

Let vRequestBody ='[';

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

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

next a

// this below is to eliminate the last ',' created

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

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;

That's all folks.. Hope this could be of a help for someone out there

Omar BEN SALEM.

[shraddha_g]

Do you mean to say the code or steps you mention above will allow to send and store qliksense data into database ?

[OmarBenSalem]

Yes indeed.

[jerifortune]

Thanks Omar,

Please, how do we achieve this at the visualization layer? Users will make some selection and click a button to trigger the POST action.

[LuisIdrovo]

Hi @OmarBenSalem Please , can you explain why it have to replace '"" twice with chr(34) and not only one?