10 Replies Latest reply: Jan 9, 2018 8:06 AM by Bjorn Wedbratt 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?

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

              Hi bmw!

              Hope u're doing well !

               

              I really need u and hope u could help

               

              As you guided me through it:

               

              I was using this:

              Let vRequestBody ='[';

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

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

              &',' & '"dateCreation":'&'"'  & Peek('DateAlertes',$(a), 'alerts')&'"'&'},';

              next a

               

              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;

               

              And that worked like a charm !

               

              But Now, I can't figure out where to put the

              WITH CONNECTION(

              BODY "$(vRequestBody)" statement:

              ------------------------------------------------------------------------------------------------

              Here's what I have :

               

              RestConnectorMasterTable:

              SQL SELECT

              "AlertID",

              "Alert_Name",

              "Alert_Date",

              "Creation_Date" AS "Creation_Date_u0",

              "ThresholdUniqueID",

              "Threshold_ID",

              "Threshold_Type",

              "Threshold_Aggregation",

              "Trading_Desk",

              "CDR_Standardized",

              "Product_Code",

              "Currency",

              "Current_Notional",

              "Reference_Notional",

              "Notional_Variation",

              "Threshold_Variation",

              "Threshold_Notional",

              "Email_Addresses",

              "__KEY_root",

              (SELECT

              "StatusID",

              "AlonotAlertID",

              "AlopatAlertID",

              "Alert_Type",

              "Status",

              "Creation_Date",

              "Creation_User",

              "__FK_AlopatAlonotStatus"

              FROM "AlopatAlonotStatus" FK "__FK_AlopatAlonotStatus"),

              (SELECT

              "@Value",

              "__FK_AlopatAlonotComments"

              FROM "AlopatAlonotComments" FK "__FK_AlopatAlonotComments" ArrayValueAlias "@Value")

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

              BODY "$(vRequestBody)"

              );

               

               

              [AlopatAlonotStatus]:

              LOAD [StatusID] AS [StatusID],

              [AlonotAlertID] AS [AlonotAlertID],

              [AlopatAlertID] AS [AlopatAlertID],

              [Alert_Type] AS [Alert_Type],

              [Status] AS [Status],

              [Creation_Date] AS [Creation_Date],

              [Creation_User] AS [Creation_User],

              [__FK_AlopatAlonotStatus] AS [__KEY_root]

              RESIDENT RestConnectorMasterTable

              WHERE NOT IsNull([__FK_AlopatAlonotStatus]);

               

               

               

               

              [AlopatAlonotComments]:

              LOAD [@Value] AS [@Value],

              [__FK_AlopatAlonotComments] AS [__KEY_root]

              RESIDENT RestConnectorMasterTable

              WHERE NOT IsNull([__FK_AlopatAlonotComments]);

               

              [root]:

              LOAD [AlertID] AS [AlertID],

              [Alert_Name] AS [Alert_Name],

              [Alert_Date] AS [Alert_Date],

              [Creation_Date_u0] AS [Creation_Date_u0],

              [ThresholdUniqueID] AS [ThresholdUniqueID],

              [Threshold_ID] AS [Threshold_ID],

              [Threshold_Type] AS [Threshold_Type],

              [Threshold_Aggregation] AS [Threshold_Aggregation],

              [Trading_Desk] AS [Trading_Desk],

              [CDR_Standardized] AS [CDR_Standardized],

              [Product_Code] AS [Product_Code],

              [Currency] AS [Currency],

              [Current_Notional] AS [Current_Notional],

              [Reference_Notional] AS [Reference_Notional],

              [Notional_Variation] AS [Notional_Variation],

              [Threshold_Variation] AS [Threshold_Variation],

              [Threshold_Notional] AS [Threshold_Notional],

              [Email_Addresses] AS [Email_Addresses],

              [__KEY_root] AS [__KEY_root]

              RESIDENT RestConnectorMasterTable

              WHERE NOT IsNull([__KEY_root]);

               

              DROP TABLE RestConnectorMasterTable;

               

               

              Even though my vRequestBody variable is correct; every time i run the script; it fails.

               

              I'm sure It's beacause of the (wrap on) "root"  PK "__KEY_root" 


              Have u faced such a thing? where do u think I can put the vRequestBody variable:


              here's the way it has been constructed:

              Let vRequestBody ='[';

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

              Let vRequestBody = vRequestBody& '{"Alert_Name" :'&'"'&   Peek('AlertName',$(a), 'Alerts')&'"'

               

              &',' & '"Creation_Date":'&'"'  & Peek('Creation_Date',$(a), 'Alerts')&'"'

              &','& '"Alert_Date":'&'"'  & Peek('Alert_Date',$(a), 'Alerts')&'"'

                &','& '"ThresholdUniqueID":'&'"'  & Peek('ThresholdUniqueID',$(a), 'Alerts')&'"'

              &','& '"Threshold_ID":'&'"'  & Peek('Threshold_ID',$(a), 'Alerts')&'"'

              &','& '"Threshold_Type":'&'"'  & Peek('Threshold_Type',$(a), 'Alerts')&'"'

              &','& '"Threshold_Aggregation":'&'"'  & Peek('Aggregation_Type',$(a), 'Alerts')&'"'

              &','& '"Trading_Desk":'&'"'  & Peek('THR.Trading_Desk',$(a), 'Alerts')&'"'

              &','& '"CDR_Standardized":'&'"'  & Peek('THR.CDR_Standardized',$(a), 'Alerts')&'"'

              &','& '"Product_Code":'&'"'  & Peek('THR.Product_Code',$(a), 'Alerts')&'"'

              &','& '"Currency":'&'"'  & Peek('Currency',$(a), 'Alerts')&'"'

              &','& '"Current_Notional":'&'"'  & Peek('Current_Notional',$(a), 'Alerts')&'"'

              &','& '"Reference_Notional":'&'"'  & Peek('Reference_Notional',$(a), 'Alerts')&'"'

              &','& '"Notional_Variation":'&'"'  & Peek('Notional_Variation',$(a), 'Alerts')&'"'

              &','& '"Threshold_Variation":'&'"'  & Peek('Threshold_Variation',$(a), 'Alerts')&'"'

              &','& '"Threshold_Notional":'&'"'  & Peek('Threshold_Notional',$(a), 'Alerts')&'"'

              &','& '"Email_Addresses":'&'"'  & Peek('Email_Addresses',$(a), 'Alerts')&'"'&'},';

               

               

              next a

               

               

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

              Let vRequestBody=vRequestBody& ']';

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

               

              when I debug it, It seems OK !

              as I told, It's the "with connection" part that is wrong

               

              Sorry for the looong msg ! I hope u could help )

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

                  Hi Omar,

                  Not sure if I can help you out here as it's a bit difficult when not having access to your data source. But anyway, if I understand it correctly, you're POST:ing a single JSON array in the request body, something like:

                   

                   

                  [{""Alert_Name"":""alert1"",""Creation_Date"":""20180101"",""Alert_Date"":""20180102"",""ThresholdUniqueID"":""AAA"",""Threshold_ID"":""III"",""Threshold_Type"":""B"",""Threshold_Aggregation"":""C"",""Trading_Desk"":""desk1"",""CDR_Standardized"":""y"",""Product_Code"":""code1"",""Currency"":""EUR"",""Current_Notional"":""A"",""Reference_Notional"":""B"",""Notional_Variation"":"""",""Threshold_Variation"":""C"",""Threshold_Notional"":""D"",""Email_Addresses"":""some@one.net""}]

                   

                  And in return you expect a JSON response similar to:

                   

                  [
                    {
                    "AlertID":"1",
                    "Alert_Name": "alert1",
                    "Alert_Date": "20180102",
                    "Creation_Date": "20180101",
                    "ThresholdUniqueID": "AAA",
                    "Threshold_ID": "III",
                    "Threshold_Type": "B",
                    "Threshold_Aggregation": "C",
                    "Trading_Desk": "desk1",
                    "CDR_Standardized": "y",
                    "Product_Code": "code1",
                    "Currency": "EUR",
                    "Current_Notional": "A",
                    "Reference_Notional": "B",
                    "Notional_Variation": "E",
                    "Threshold_Variation": "C",
                    "Threshold_Notional": "D",
                    "Email_Addresses": "some@one.net",
                    "AlopatAlonotStatus": [
                    {
                    "StatusID": "c4JPSDd",
                    "AlopatAlertID": "c4JPSDa",
                    "Alert_Type": "radio",
                    "status":"1",
                    "Creation_Date": "2016-09-22T09:29Z",
                    "Creation_User": "User1"
                    },
                    {
                    "StatusID": "c4JPSDe",
                    "AlopatAlertID": "c4JPsDa",
                    "Alert_Type": "radio",
                    "status":"2",
                    "Creation_Date": "2017-09-22T09:29Z",
                    "Creation_User": "User2"
                    }
                    ],
                    "values": [
                    "value1",
                    "value2",
                    "value3"
                    ]
                    }
                  ]
                  

                   

                  If so, the WITH CONNECTION statement is in the right place, just after the line (wrap on) "root"

                   

                  SQL SELECT 
                  "AlertID",
                  "Alert_Name",
                  "Alert_Date",
                  "Creation_Date" AS "Creation_Date_u0",
                  "ThresholdUniqueID",
                  "Threshold_ID",
                  "Threshold_Type",
                  "Threshold_Aggregation",
                  "Trading_Desk",
                  "CDR_Standardized",
                  "Product_Code",
                  "Currency",
                  "Current_Notional",
                  "Reference_Notional",
                  "Notional_Variation",
                  "Threshold_Variation",
                  "Threshold_Notional",
                  "Email_Addresses",
                  "__KEY_root",
                  (SELECT 
                  "StatusID",
                  "AlopatAlertID",
                  "Alert_Type",
                  "status",
                  "Creation_Date",
                  "Creation_User",
                  "__FK_AlopatAlonotStatus"
                  FROM "AlopatAlonotStatus" FK "__FK_AlopatAlonotStatus"),
                  (SELECT 
                  "@Value",
                  "__FK_values"
                  FROM "values" FK "__FK_values" ArrayValueAlias "@Value")
                  FROM JSON (wrap on) "root" PK "__KEY_root"
                  WITH CONNECTION(
                  BODY "$(vRequestBody)"
                  );
                  

                   

                  Pay attention that the SQL SELECT must match the expected JSON response. The nested (SELECT) statements should match the structure/levels of the returned JSON response as seen above.

                   

                  Hope this will give you some clues to troubleshoot the script.

                   

                  Best,

                  Bjorn