1 Reply Latest reply: Dec 26, 2017 10:18 AM by omar bensalem RSS

    Qlik REST Connector (@Post)

    omar bensalem

      Hi everybody ( bmw )

       

      I'm trying to use Qlik REST Connector to send one table to the WS.

       

      here's an exp for the web service (Post method):

      Capture.PNG

       

       

      This is how I created the connection:

      Capture.PNG

       

      I test it and It works just fine!

       

      Here's its initial script:

      LIB CONNECT TO 'Post Alerts';

       

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

       

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

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

      Now, here's what I've done:

       

      Let vRequestBody ='[';

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

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

       

       

      &',' & '"Creation_Date_u0":'&'"'  & 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));

       

       

      LIB CONNECT TO 'Post Alerts';

       

       

      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;

       

      drop Table Alerts;

       

      But, when I reload:

      i HAVE THIS ERROR:

      Capture.PNG

      Capture.PNG

       

      Capture.PNG

       

      When I debug, this is what I have:

      here's the variable:

      Capture.PNG

       

      ps : in a test I've previously done; even with "" instead of "; it works fine !

       

      Capture.PNG

       

      rewind; here's the model for the excpected json msg:

      Capture.PNG

      Exp:

      [

        {

          "AlertID": 0,

          "Alert_Name": "string",

          "Alert_Date": "2017-12-26T11:50:46.038Z",

          "Creation_Date": "2017-12-26T11:50:46.038Z",

          "ThresholdUniqueID": 0,

          "Threshold_ID": 0,

          "Threshold_Type": "string",

          "Threshold_Aggregation": "string",

          "Trading_Desk": "string",

          "CDR_Standardized": "string",

          "Product_Code": "string",

          "Currency": "string",

          "Current_Notional": 0,

          "Reference_Notional": 0,

          "Notional_Variation": 0,

          "Threshold_Variation": 0,

          "Threshold_Notional": 0,

          "Email_Addresses": "string"

        }

      ]

       

       

      Any hint please?