8 Replies Latest reply: Nov 9, 2017 5:33 AM by omar bensalem RSS

    Qlik Rest Connector Post Method (into the script)

    omar bensalem

      Hi guy, bmw,


      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?


      Thanks a lot !!

        • Re: Qlik Rest Connector Post Method (into the script)
          Bjorn Wedbratt

          Hi Omar,

          To change the BODY in the request you can use WITH CONNECTION() statement in the SELECT-FROM statement for the Rest connector. Look for the row "FROM JSON (wrap on) "root" PK "__KEY_root" when reading in the RestConnectorMasterTable:

          Here you can add the WITH CONNECTION() statement to alter the BODY:



          FROM JSON (wrap on) "root" PK "__KEY_root"

          WITH CONNECTION( BODY "$(vRequestBody)")



          I'm using a variable which will contain the full Request body text, based on additional variable(s), something like:


          LET vMyvar = 'Stephen';


          LET vRequestBody ='{';

          Let vRequestBody = vRequestBody&'"name":"$(vMyvar)",';

          Let vRequestBody = vRequestBody&'"age":"30",';

          Let vRequestBody = vRequestBody&'"cars":{';

          Let vRequestBody = vRequestBody&'"car1":"Ford",';

          Let vRequestBody = vRequestBody&'"car2":"BMW"';

          Let vRequestBody = vRequestBody&'}';

          Let vRequestBody = vRequestBody&'}';

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


          The above will translate into the following JSON body:


              "name":"Stephen", "age":30, "cars": {







          Note that you need to replace the quote (") using chr(34) or you will get an error on load.



          Attached is a full example running against postman-echo. Hope it helps.





            • Re: Qlik Rest Connector Post Method (into the script)
              omar bensalem

              Hi bmw ,

              I'm trying to store all of the alerts in one shot :

              //This is the original table to collect


              //Here, it's mandatory to format the table in such a format

              load    date(Date#(date,'DD/MM/YYYY'),'DDMMYYYY') as date, region,trade,sales;

              load * Inline [

              date, region, trade, sales

              14/11/2017, Paris, 2 , 900

              14/11/2017, Marseille, 2, 800

              14/11/2017, Lyon, 2, 800




              //This is the alert table, in which we create the new alerts, these alerts will be sent to the WS to test it


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

              load alert, left(alert,8) as date,Desk  where len(alert) >20;

              load label&' sales2000 trade3' as alert,Desk where label <> '-';

              load if(sum(trade2)<3 and sum(sales2)<2000, label) as label,Desk group by label,Desk;

              load label, sum(trade2) as trade2 , sum(sales2) as sales2 ,Desk group by label,Desk;

              load  region as Desk, date&region as label,(trade) as trade2 , (sales) as sales2  Resident table;

              load DESK2 as DESK Resident alerts;

              DROP Table table;


              //here's what I did:


              Let vRequestBody ='[';

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


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

              next a

              Let vRequestBody=vRequestBody& ']';



              LIB CONNECT TO 'Post Alert';




              SQL SELECT





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

              BODY "$(vRequestBody)"





              LOAD [alertName] AS [alertName],

              [dateCreation] AS [dateCreation],

              [dateValidation] AS [dateValidation],

              [utcodeValidator] AS [utcodeValidator]

              RESIDENT RestConnectorMasterTable;





              DROP TABLE RestConnectorMasterTable;


              here's the variable content:



              now, when i load, I have this error:



              Can you please assist me on this?