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 · Accepted solution]

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]

Thank you very much !

[OmarBenSalem]

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:

now, when i load, I have this error:

Can you please assist me on this?

[OmarBenSalem]

If I add this:

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

Ill have:

[Bjorn_Wedbratt]

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]

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:

result:

[Bjorn_Wedbratt]

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]

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]

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 )