Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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):
This is how I created the connection:
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:
When I debug, this is what I have:
here's the variable:
ps : in a test I've previously done; even with "" instead of "; it works fine !
rewind; here's the model for the excpected json msg:
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?
Accepted Solutions
