I can access the URL of the following specifications by REST connector. 1) I can get 500 records per 1 REST request. 2) I can get sorted records with the '$id' column by query parameters. ex: $ID > 523
There are no problems if the number of records is 500 or less because I can get 1 request. And the problem is over 500 records.
I can get more than 500records without problems with the script below, but it does not look elegant.
In VB and C #, the last '$id' value can be got by getting the value every time in the loop and passing through the loop.
In QlikView ,Is there any other way to get max function? I tried using peek function peek('xxx',-1), but I got null.
------------------------------------------- Let startId=0; Let Recordcount=0; Let PageCount=0;
Do Let LastRecordcount=Recordcount;
RestConnectorMasterTable: Load *, "__FK_$id" + (500*$(PageCount)) as __FK_$id_temp;
SQL SELECT "__KEY_records", (SELECT "value" AS "value_u4", "__FK_$id" FROM "$id" FK "__FK_$id") FROM JSON (wrap off) "records" PK "__KEY_records" WITH CONNECTION( QUERY "query" "$id > $(startId) order by $id" );
NoConcatenate TempTable: Load max(value_u4) as _max_id ,count(value_u4) as _record_count Resident RestConnectorMasterTable;
Let startId=peek('_max_id',0,'TempTable'); Let Recordcount=peek('_record_count',0,'TempTable');
DROP TABLE TempTable;
Let PageCount=PageCount+1;
Loop until LastRecordcount=Recordcount
[$id]: LOAD [value_u4] AS [kintone_id], [__FK_$id_temp] AS [__KEY_records] RESIDENT RestConnectorMasterTable WHERE NOT IsNull([__FK_$id]);
