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?
