Passing parameters into REST API connection

kateperry — Tue, 21 Dec 2021 14:01:50 GMT

I am trying to load data from Adaptive Insights using REST connection.

When I setup the rest connection, I specify the:

URL https://api.adaptiveinsights.com/api/v19

Method: POST

Request Body:

<?xml version='1.0' encoding='UTF-8'?>
<call method="exportData" callerName="Qlik">
<credentials login="xxxxx" password="xxxxx"/>
<version name ="xxxxx"/>
<format useInternalCodes="true"/>
<filters>
<accounts>
<account code = "Revenue" isAssumption="false" includeDescendants="true"/>
</accounts>
</filters>
<rules includeRollups="true">
<currency useCorporate="true"/>
</rules>
</call>

I then have the following data returned, with success = true and the whole dataset returned in a csv format.

I need to make the "version name" parameter dynamic in the script, so I have been trying to use the "With Connection" syntax.

Let vRequestBody ='';
let vRequestBody = vRequestBody &'<?xml version="1.0" encoding="utf-8"?>';
let vRequestBody = vRequestBody &'<call method="exportData" callerName="Qlik">';
let vRequestBody = vRequestBody &'<credentials login="xxxxx" password="xxxxx"/>';
let vRequestBody = vRequestBody &'<version name ="xxxxx"/>';
let vRequestBody = vRequestBody &'<format useInternalCodes="true"/>';
let vRequestBody = vRequestBody &'<filters>';
let vRequestBody = vRequestBody &'<accounts>';
let vRequestBody = vRequestBody &'<account code = "Revenue" isAssumption="false" includeDescendants="true"/>';
let vRequestBody = vRequestBody &'</accounts>';
let vRequestBody = vRequestBody &'</filters>';
let vRequestBody = vRequestBody &'<rules includeRollups="true">';
let vRequestBody = vRequestBody &'<currency useCorporate="true"/>';
let vRequestBody = vRequestBody &'</rules>';
let vRequestBody = vRequestBody &'</call>")';

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

RestConnectorMasterTable:
SQL SELECT
"attr:success" AS "success",
"output"
FROM XML "response" QDL
WITH CONNECTION (URL "https://api.adaptiveinsights.com/api/v23",
BODY "$(vRequestBody)"
);

This doesn't error, but I don't get any results.

I don't suppose anyone has an idea why this might be??

I am using the same connection for both.

Many thanks in anticipation of any help!

Kate

Re: Passing parameters into REST API connection

petter — Sun, 02 Feb 2020 06:41:37 GMT

Have a look at this line:

let vRequestBody = vRequestBody &'</call>")';

Is the last two characters in the string something that should really be there. To me it looks like something that shouldn't be there...

Re: Passing parameters into REST API connection

kateperry — Mon, 03 Feb 2020 09:47:19 GMT

Thank you so so so so much for responding with that. You are 100% correct. Removing those characters returned the data perfectly.

You would not believe how many hours I spent looking at this and I still missed it. What a silly copy/paste error to have caused me such a headache!

You're a star!!!!! Much appreciated.

topic Re: Passing parameters into REST API connection in Connectivity & Data Prep

Passing parameters into REST API connection

Re: Passing parameters into REST API connection

Re: Passing parameters into REST API connection