Qlik Sense - Rest API (Json) + Pagination + Incremental load (new and update)

BSo — Sun, 03 Sep 2023 20:13:59 GMT

Hey, I'm thinking how to apply incrementation (insert and update) when downloading data to a QVD file via REST API (JSON file) and pagination to pages with 100 records (required) at the same time.

The data retrieved is all the data since the last change.

I have done the data download with pagination at the moment. And now I wonder where to put the incrementation

Has anyone done something like this before?

// 1 ------------------------------------------------------------------------------------

LIB CONNECT TO 'Get token';

RestConnectorMasterTable:
SQL SELECT 
 "accessToken",
 "refreshToken"
FROM JSON (wrap on) "root";

[root]:
LOAD [accessToken],
     [refreshToken]
RESIDENT RestConnectorMasterTable;
DROP TABLE RestConnectorMasterTable;

LET vAccessTokenValue = Peek('accessToken');

// 2 ------------------------------------------------------------------------------------

LIB CONNECT TO 'With Access Token';

RestConnectorMasterTable:
SQL SELECT 
	"totalPages",
	"__KEY_root"
FROM JSON (wrap on) "root" PK "__KEY_root"

WITH CONNECTION (
URL "https://{URLAddress}/search?modifiedOn=2023-09-01T00:00:00.000Z&size=100",
HTTPHEADER "Authorization" "Bearer $(vAccessTokenValue)"
);

[root]:
LOAD	[totalPages]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__KEY_root]);
DROP TABLE RestConnectorMasterTable;

let vTotalFetched = FieldValue('totalPages', 1);

// 3 ------------------------------------------------------------------------------------

let vStartAt = 0; 
set vPageSize = 100;

Do While vStartAt <= vTotalFetched
let vUrl = 'https://{URLAddress}/search?modifiedOn=2023-09-01T00:00:00.000Z&size=' & $(vPageSize) & '&page=' & $(vStartAt) & 'sort=id';

RestConnectorMasterTable:
SQL SELECT
(...)
WITH CONNECTION (
URL "$(vUrl)",
HTTPHEADER "Authorization" "Bearer $(vAccessTokenValue)"
);

Store RestConnectorMasterTable into [lib://StageFile/RestConnectorMasterTable.qvd](qvd);

(...)

[orderContact]:
LOAD	[email],
	[customerNo],
	[firstName],
	[lastName],
	[__FK_orderContact] AS [__KEY_content]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_orderContact]);

(...)

DROP TABLE RestConnectorMasterTable;

Let vStartAt=vStartAt+1;

Loop

topic Qlik Sense - Rest API (Json) + Pagination + Incremental load (new and update) in Integration, Extension & APIs

Qlik Sense - Rest API (Json) + Pagination + Incremental load (new and update)