Qlik Rest Connector Post Method (into the script)

[OmarBenSalem]

Hi guy, bmw‌,

I have created this connection :

authentication schema : BASIC

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 !!

[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

[OmarBenSalem]

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 !

[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

[OmarBenSalem]

Thank you for your response;

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

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

[khasimvali85]

Hi Team,

I am also facing same kind of issue where dynamically changing the values from script using variable as follows.

if i am passing the values as character and number into the variable(vtokenvalue ) the script working fine, while passing the value like (?Hu7?$(\"e) getting the error from attached screen.

Please suggested me to resolve this issues.

Please find attached sample script for reference.

Thanks in Advance!!

Thanks & Regards,

Khasim

[thuaume]

Hello Kashim, 

did you go further on this issue? I'll be interested to  get the solution. 

Thank you 

Best regards 

Thibaut