Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
OmarBenSalem

Qlik Rest Connector Post Method (into the script)

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

1 Solution

Accepted Solutions
Bjorn_Wedbratt
Former Employee
Former Employee

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

View solution in original post

15 Replies
Bjorn_Wedbratt
Former Employee
Former Employee

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

OmarBenSalem
Author

Thank you very much !

OmarBenSalem
Author

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?

OmarBenSalem
Author

If I add this:

let vRequestBody = replace(vRequestBody,'"', chr(34)&chr(34));

Ill have:

Capture.PNG

Bjorn_Wedbratt
Former Employee
Former Employee

Hi Omar,

I think you're almost at it, but you need to take care of that extra comma you will get at the end when looping through for a=0 to....

You could just do something like:

Let vRequestBody = vRequestBody& '{"alert" :'&chr(34)&chr(34)& vAlert & Peek('alert',$(a), 'alerts')&'"'&',' & '"date":'&'"'& vDateCreation & Peek('DateAlertes',$(a), 'alerts')&'"'&'},';


next a

Let vRequestBody = left(vRequestBody,len(vRequestBody)-1);

Let vRequestBody=vRequestBody& ']';

See if that helps.

OmarBenSalem
Author

Thanks for the response; here's what I did:

Let vRequestBody ='[';

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

Let vRequestBody = vRequestBody& '{"alertName" :'&chr(34)&chr(34)& vAlert & Peek('alert',$(a), 'alerts')&'"'&',' & '"dateCreation":'&'"'& vDateCreation & Peek('DateAlertes',$(a), 'alerts')&'"'&'},';

next a

Let vRequestBody = left(vRequestBody,len(vRequestBody)-1);

Let vRequestBody=vRequestBody& ']';

variable:

Capture.PNG

result:

Capture.PNG

Bjorn_Wedbratt
Former Employee
Former Employee

Hmm...now you got two double quotes in the alertName value in the variable, i.e:

{"alertName":""09112017Paris sales2000 trade3",....

try and remove one of the Chr(34).

It's important that the JSON request is properly formatted and it can be quite tricky to get the variable for the request body correct, especially with quoting.

OmarBenSalem
Author

this works

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

OmarBenSalem
Author

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 )