Hello,everybody!
Sorry for my poor English.
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]);
DROP TABLE RestConnectorMasterTable;
exit Script;
-------------------------------------------
In addition, __KEY_records adds 500 * $ (PageCount) in order to obtain the value of 1 to 500 cyclically every time Do-Loop.
Please Help me.
