Qlik Rest Connector pagination problem

[Jonathan_Alm]

Hi,

I am trying to go through a series of records to be able to fetch all the data.

Today I do this by looping through all records, but since I received the user guide I see that this could be solved through pagination.

However I fail when I try to implement this.

The URL is setup like this:

http://aaa.bbb.local:8020/api/platform/person/v1/persons/?_offset=0

The parameter is "?_offset"

The call retrieves 1000 rows.

To get 1000->2000 I enter

http://aaa.bbb.local:8020/api/platform/person/v1/persons/?_offset=1000‌‌

If I enter this into chrome Rest Api Client I get the following:

- "page": [1000]

0: 

{

"id": "2335AA2-212-2AA"

"civicNo": "1234567"

"firstName": "Carl"

"lastName": "Smith"

"modifiedTime": 1428919220291

}

1: 

{

"id": "2325FG2-222-313"

"civicNo": "1234321"

"firstName": "Marco"

"lastName": "Polo"

"modifiedTime": 1429004337935

}

"queryTotal": 3502

"pageSize": 1000

}

page: [1000] means that there are 1000 posts fetched

queryTotal means that there are 3502 rows in total

pageSize is unclear. Both page and pageSize are always set to 1000.

If I enter the URL in QLIK I get:

RestConnectorMasterTable:

SQL SELECT

  "id",

  "civicNo",

  "firstName",

  "lastName",

  "modifiedTime"

FROM JSON (wrap off) "page";

[page]:

LOAD

  [id],

  [civicNo],

    [firstName],

    [lastName],

  [modifiedTime]

RESIDENT RestConnectorMasterTable;

DROP TABLE RestConnectorMasterTable;

So my question is:

How do I set up the pagination to be able to fetch the following 3502 rows, which means that it should iterate 4 times to fetch all?

Kind regards, Jonathan

[petter]

If you select Pagination of Offset which is what your REST source demands, the connector also needs information about where to find the total number of rows to fetch. That information can be returned either in the Response Header or in the data itself. That varies with various sources and the way they have chosen to implement their RESTful API.

You need to fill out both the Query Parameter with the Offset and also specify it in the section for Pagination.

So it should look something like this:

Maybe the "Total field path" needs to be adjusted ... but I think it should be right...

[Jonathan_Alm]

Hi,

thanks for your help. I have tried your solution, but I still only get the first 1000 posts.

I will see if I can get hold of the developer of the API and ask that person what is missing. Maybe there is something with the API that does not support the call.

I will let you know what I come up with.

Kind regards, Jonathan

[petter]

Looking closer at your JSON result data from Chrome I can see that you actually get the necessary information that the Qlik REST Connector needs to know the total number of rows to get. But you don't retrieve this information as part of the SQL SELECT statement. Then there is no way for the REST-connector to know how it will reach the end of data and it will refuse to iterate. Unfortunately it does not send any warning or error message to that effect.

You should retrieve on table with your real data and one table which contains queryTotal and pageSize. Then you will be able to in the REST Connector dialog box to enter the queryTotal. But just entering it in the dialog box without retrieving the data and not specifying the right path to the data will not make it work.

It is a bit hard to give you exact specification on exactly what to enter where when I don't see the screenshots and preview that you are getting when you do your connect. Is it possible for you to share that with us?

[#5 · Accepted solution]

See the example with JIRA: https://help.qlik.com/en-US/connectors/Subsystems/REST_connector_help/Content/1.0/Create-REST-connec...

[Marius_]

I ran into the following error:

HTTP protocol error 400 (Bad Request):{"message":"Page size cannot be larger than 1,000.","code":501}

My CUSTOM CONNECT TO "Provider=QvRestConnector.exe;url=https://api.clockify.me read as follows:

....;queryParameters=page-size%25000%1;....

which means that I extended the page size to 5000

To fix this error I made the following changes:

I just changed the page-size to 999

and ad queryTotal%299999%1   [total lines off 99999] 

......;queryParameters=page-size%2999%1queryTotal%299999%1;........

;queryParameters=page-size%2999%1queryTotal%22010%1;