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

    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):

    Capture.PNG

    Capture.PNG

    Capture.PNG

     

    3) Import the data within the WS:

    Capture.PNG

     

    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.