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 :

      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?

       

      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": {

                  "car1":"Ford",

                  "car2":"BMW",

                  "car3":"Fiat"

              }

          }

           

          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.

           

           

          Best,

          Bjorn

            • 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

              table:

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

              ];

               

              NoConcatenate

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

              alerts:

              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';

               

               

              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;

               

              here's the variable content:

              Capture.PNG

               

              now, when i load, I have this error:

              Capture.PNG

               

              Can you please assist me on this?