Dynamic loading of paged API

jelindbe — Tue, 22 Mar 2022 09:47:10 GMT

Hello,

I am trying to load statistical data from a paged api into Qlik sense

This is the api url

URL https://api.test/api/Dataset/9526_Naturlig%20vekst%20Vestby/FODTERES?page=1&resultPerPage=5

Result from call:

projectId,date,pk,alternative,GRP,PLS,PER,_VALUE
eo2XGo8C2gg,10-03-22 12:11:28,ccntOUhQ2U203qequl8fiA,Alternativ.1,test,v,2005,33
eo2XGo8C2gg,10-03-22 12:11:28,F7JLY+MVsUy8pEp5RoeO5w,Alternativ.1,v,a,2006,33
eo2XGo8C2gg,10-03-22 12:11:28,6jHzLiVkL0CjuovQFrN+nA,Alternativ.1,v,b,2007,11
eo2XGo8C2gg,10-03-22 12:11:28,6KOdvpjY+EqyMcozhs9RTg,Alternativ.1,v,c,2008,12

My script in Qlik sense:

let dataset = 'FODTERES';
let vResultPerPage = 5;
let project = '9526_Naturlig%20vekst%20Vestby';

TotalCount:
//find max pages for a given dataset

SQL SELECT
"X-Paging-TotalRecordCount",
"X-Paging-PageCount",
"__KEY__response_header"
FROM CSV "_response_header" PK "__KEY__response_header"
WITH CONNECTION(Url "https://api.test/api/Dataset/$(project)/$(dataset)?page=1&resultPerPage=5");

let vMaxPage = peek('X-Paging-PageCount',-1,'TotalCount'); //max pages to call

DROP TABLE TotalCount;

For startAt = 0 to vMaxPage step 1

RestConnectorMasterTable:

SQL SELECT
"projectId",
"date",
"pk",
"alternative",
"GRP",
"PLS",
"PER",
"_VALUE"
FROM CSV (header on, delimiter ",", quote """") "Data"
WITH CONNECTION(Url"https://api.test/api/Dataset/$(project)/$(dataset)page=$(startAt)&resultPerPage=$(vResultPerPage)");

NEXT startAt;

[CSV_source]:
LOAD [projectId],
[date],
[pk],
[alternative],
[GRP],
[PLS],
[PER],
[_VALUE]
RESIDENT RestConnectorMasterTable;

DROP TABLE RestConnectorMasterTable;

The looping seems to work as it iterates and updates the WITH CONNECTION with correct variables, but only the last page of data is saved into CSV_source. Any ideas of how to get all the data into CSV_source?

topic Dynamic loading of paged API in Integration, Extension & APIs

Dynamic loading of paged API