Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

OmarBenSalem
Esteemed Contributor

Qlik REST Connector (@Post)

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?

1 Solution

Accepted Solutions
OmarBenSalem
Esteemed Contributor

Re: Qlik REST Connector (@Post)

The problem was the way number are formatted in the web service (spaces are forbidden lol; exp: 3459 is correct;

3 459 is not accepted..)

1 Reply
OmarBenSalem
Esteemed Contributor

Re: Qlik REST Connector (@Post)

The problem was the way number are formatted in the web service (spaces are forbidden lol; exp: 3459 is correct;

3 459 is not accepted..)

Community Browser