Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
Partner
Partner

Cyclic REST Connector

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.

Labels (2)