13 Replies Latest reply: Jun 7, 2018 7:05 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?

            • 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

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

                      Hi bmw, hope you're doing well; tell me;

                       

                      I was trying to make a body request as follow:

                       

                      let MsgBody='{"message": "<messageML>HelloQlik</messageML>"}';
                      let MsgBody=replace(MsgBody,'"',chr(34) & chr(34));


                      But this does not seem to work, I have an error each time I run my script..

                      can you see why? Is there an obvious syntaxical mistake I'm making?


                      Thanks !

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

                          Hi Omar,

                          Just tested your body real quick towards Postman Echo, where you can simply echo back the request using https://postman-echo.com/post and it seems to work fine.

                           

                          Might be the response getting back doesn't match the SQL SELECT statement. Is the response in JSON as well? Typically REST Connector will auto detect the response received, but you can also try and change it from JSON to CSV/XML in the select wizard.

                           

                          Best,

                          Bjorn

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

                              Thank you for your response;

                              I've also tested it in postman and it does work, here's the response:

                              Capture.PNG

                              and here's the QLIKVIEW script:

                              let MsgBody='{"message": "<messageML>HelloQlik</messageML>"}';
                              let MsgBody=replace(MsgBody,'"',chr(34) & chr(34));

                               

                              SQL SELECT
                              "messageId",
                              "timestamp",
                              "message",
                              "__KEY_root",
                              (SELECT
                              "@Value"
                              FROM
                              "attachments" FK "__FK_Attachments" ArrayValueAlias "@Value"), 
                              (SELECT
                              "userId",
                              "displayName",
                              "email",
                              "username",
                              "__FK_User"
                              FROM
                              "user" FK "__FK_User"),
                              (SELECT
                              "streamId",
                              "streamType",
                              "__FK_Stream"
                              FROM
                              "stream" FK "__FK_Stream")
                              FROM JSON (wrap on)
                              "root" PK "__KEY_root"
                              WITH CONNECTION (
                              //URL "$(apicall)",
                              HTTPHEADER
                              "Content-Type" "application/json",
                              HTTPHEADER
                              "sessionToken" "$(token)",
                              HTTPHEADER
                              "keyManagerToken" "$(kmtoken)",
                              BODY
                              "$(MsgBody)"
                              );