Skip to Content
Data Plus ServerHow ToAPI call from Qlik script

API call from Qlik Script

Simple upsert call to the DB API.

1. Create a placeholder POST data connection

  • Create a Rest data connection
  • Set the URL to an exampl API, e.g. https://jsonplaceholder.typicode.com/posts 
  • Method POST
  • Allow “WITH CONNECTION”
  • The example API has to respond sucessfully, otherwise the data connection cannot be created

2. Qlik script

let vUrl = 'https://xxxx.dataplus.leitart.de/api/db/table/[table name]/upsert';
let vApiKey = 'xxxxxxxx';
let vBody = '{"id":"123", "col1":"val1"}';
vBody = Replace(vBody,'"','""');
LIB CONNECT TO 'Basic Rest Post';
RestConnectorMasterTable:
SQL SELECT "1" FROM JSON (wrap on) "root"
WITH CONNECTION(
URL "$(vUrl)",
HTTPHEADER "Content-Type" "application/json",
HTTPHEADER "Authorization" "$(vApiKey)",
BODY "$(vBody)"
);
DROP TABLE RestConnectorMasterTable;
  • Set the URL to your Data+ Server and the table to edit
  • Set the API key
  • Set your desired body
  • Due to Qlik escape characters, all ” in the body have to be duplicated
  • Please note, if your body includes text with ” the JSON has be to properly handled as well
  • Use the previously created data connection

3. Bulk upsert script

UpsertTable:
Load * Inline [
id, col1
 1, val1
 2, val2
 3, val3
];

let vUrl = 'http://xxxx.dataplus.leitart.de/api/db/table/[table name]/bulk/upsert';
let vApiToken = 'xxxxxxxx';

LET vBody = '[';

FOR i = 0 TO (NoOfRows('UpsertTable') - 1)
	if(i>0) then
		vBody = vBody & ',';
    end if;
 	LET vId 		= Peek('id',i,'UpsertTable');
 	LET vCol1  		= Peek('col1',i,'UpsertTable');
	vBody = vBody & '{"id":"$(vId)", "col1":"$(vCol1)"}';
next i;

vBody = vBody & ']';

vBody = Replace(vBody,'"','""');

LIB CONNECT TO 'Basic Rest Post';
RestConnectorMasterTable:
SQL SELECT "1" FROM JSON (wrap on) "root"
WITH CONNECTION(
URL "$(vUrl)",
HTTPHEADER "Content-Type" "application/json",
HTTPHEADER "Authorization" "$(vApiToken)",
BODY "$(vBody)"
);
DROP TABLE RestConnectorMasterTable;
Last updated on
Microsoft Entra Login