Qlik Community

Ask a Question

Qlik Sense Integration, Extensions, & APIs

Discussion board where members can learn more about Integration, Extensions and API’s for Qlik Sense.

Announcements
March 11, 2PM EST: Do More with Qlik - Getting Started wtih Qlik Sense SaaS - Redux. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Kevin-Marlow
Contributor
Contributor

Rest API - Pagination Not Working

Hi,

I'm using the REST API connector and I'm successfully able to connect to the data source, however the JSON service will only return 100 items per page and I need to pull 1,000's of items.  I have checked the Response Header and the next page is  displayed as 'link' item.  If I select 'Pagination', 'Next URL' and enter '_response_header/link' as the 'Next URL' path, check 'Look in header' and run the reload it still only loads the first 100 items and never goes to the second page.  Am I misunderstanding the use of 'Next URL' or is this functionality not supported?

Thanks,

Kevin

4 Replies
Levi_Turner
Employee
Employee

Let's take an example which is actually how Qlik Cloud Services (QCS) forms their responses.

Response:

{
  "data": [
      {
          "name": "AppName",
          "resourceAttributes": {
              "_resourcetype": "app",
              "createdDate": "2019-12-12T17:43:32.964Z",
              "description": "",
              "dynamicColor": "",
              "hasSectionAccess": false,
              "id": "2694b8ce-0711-4bb7-ab98-08ca66daa45c",
              "lastReloadTime": "2019-12-12T17:46:12.060Z",
              "modifiedDate": "2019-12-12T17:46:12.461Z",
              "name": "AppName",
              "originAppId": "",
              "owner": "DOMAIN\\UserId",
              "ownerId": "ownerID",
              "publishTime": "",
              "published": false,
              "spaceId": "",
              "thumbnail": ""
          }
    }
  ],
  "links": {
      "self": {
          "href": "https://fake-tenant.us.qlikcloud.com/api/v1/items"
      },
      "next": {
          "href": "https://fake-tenant.us.qlikcloud.com/api/v1/items?next=FgAAAAdfaWQAXj28vOUOhQABwV2UAA"
      }
  }
}

 

For the Qlik REST Connector, the config for pagination URL would be:

  • root/links/next/href

The root bit is needed as an artifact of the Qlik REST Connector. The remainder is a straight-forward path to the element in the response.

Hope that helps.

Kevin-Marlow
Contributor
Contributor

Maybe it's a limitation of the API I'm using but we don't see at the root level.  We are using the FreshService API:  https://api.freshservice.com/v2/#pagination

Pagination

API responses that return a list of objects, such as View List of Tickets are paginated. To scroll through the pages, add the parameter page to the query string. The page number starts with 1.

https://domain.freshservice.com/api/v2/tickets?page=2

By default, the number of objects returned per page is 30. This can be adjusted by adding the per_page parameter to the query string. The maximum number of objects that can be retrieved per page is 100. Invalid values and values greater than 100 will result in an error.

https://domain.freshservice.com/api/v2/tickets?per_page=10

The per_page and page parameters can also be used together. The following example will retrieve the 11th to 20th tickets.

https://domain.freshservice.com/api/v2/tickets?per_page=10&page=2

The 'link' header in the response will hold URL of the next page, if it exists. If you have reached the last page of objects, then the link header will not be set.

Headers:
"link":< https://domain.freshservice.com/api/v2/tickets?filter=all_tickets&page=2>;rel="next"

The Qlik Connector is generating the following code:

RestConnectorMasterTable:
SQL SELECT
"Transfer-Encoding",
"Connection",
"status" AS "status_u0",
"x-freshservice-api-version",
"pragma",
"x-xss-protection",
"x-request-id",
"link",
"x-frame-options",
"x-content-type-options",
"x-envoy-upstream-service-time",
"x-fw-ratelimiting-managed",
"x-ratelimit-total",
"x-ratelimit-remaining",
"x-ratelimit-used-currentrequest",
"Cache-Control",
"Content-Type",
"Date",
"Expires",
"__KEY__response_header"
FROM JSON "_response_header" PK "__KEY__response_header";

[_response_header]:
LOAD [Transfer-Encoding],
[Connection],
[status_u0],
[x-freshservice-api-version],
[pragma],
[x-xss-protection],
[x-request-id],
[link],
[x-frame-options],
[x-content-type-options],
[x-envoy-upstream-service-time],
[x-fw-ratelimiting-managed],
[x-ratelimit-total],
[x-ratelimit-remaining],
[x-ratelimit-used-currentrequest],
[Cache-Control],
[Content-Type],
[Date],
[Expires],
[__KEY__response_header]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__KEY__response_header]);


DROP TABLE RestConnectorMasterTable;

and I'm see the following response:

Date: Tue, 03 Mar 2020 16:35:38 GMT
Content-Type: application/json; charset=utf-8
Transfer-Encoding: chunked
Connection: keep-alive
status: 200 OK
cache-control: no-cache, no-store, max-age=0, must-revalidate
x-freshservice-api-version: latest=v2; requested=v2
pragma: no-cache
x-xss-protection: 1; mode=block
x-request-id: 4952f88f-9436-4254-8f99-efd13dcf84bc
link: <https://freshservice.com/api/v2/tickets?page=2>; rel="next"
x-frame-options: SAMEORIGIN
x-content-type-options: nosniff
expires: Wed, 13 Oct 2010 00:00:00 UTC
x-envoy-upstream-service-time: 45
x-fw-ratelimiting-managed: true
x-ratelimit-total: 5000
x-ratelimit-remaining: 4998
x-ratelimit-used-currentrequest: 1

AHMB
Contributor
Contributor

were you able to resolve this issue?

 

I'm facing the same problem with Freshdesk REST API.   the pagination using "Next URL" does not work.

Kevin-M
Contributor
Contributor

Yes, but I stopped using Pagination and started using 'With Connection'

Let startAt = 1;
Set Page = 500;
Let ValidateCount= 0;
Let FirstRun = 1;
For startAt = 1 to $(Page) step 1

LIB CONNECT TO 'FreshService_Tickets';

RestConnectorMasterTable:
SQL SELECT
"fr_escalated",
"spam",
"email_config_id",
"group_id",
"priority",
"requester_id",
"responder_id",
"source",
"status",
"subject",
"department_id",
"id",
"type",
"due_by",
"fr_due_by",
"is_escalated",
"description",
"description_text",
"category",
"sub_category",
"item_category",
"created_at",
"updated_at",
"deleted",
"to_emails",
"__KEY_tickets",
(SELECT
"@Value",
"__FK_cc_emails"
FROM "cc_emails" FK "__FK_cc_emails" ArrayValueAlias "@Value"),
(SELECT
"@Value" AS "@Value_u0",
"__FK_fwd_emails"
FROM "fwd_emails" FK "__FK_fwd_emails" ArrayValueAlias "@Value_u0"),
(SELECT
"@Value" AS "@Value_u1",
"__FK_reply_cc_emails"
FROM "reply_cc_emails" FK "__FK_reply_cc_emails" ArrayValueAlias "@Value_u1"),
(SELECT
"@Value" AS "@Value_u2",
"__FK_to_emails"
FROM "to_emails" FK "__FK_to_emails" ArrayValueAlias "@Value_u2"),
(SELECT
"__FK_custom_fields"
FROM "custom_fields" FK "__FK_custom_fields")
FROM JSON (wrap off) "tickets" PK "__KEY_tickets"
WITH CONNECTION(Url "https://<company>.freshservice.com/api/v2/tickets?per_page=100&page=$(startAt)");

Let CurrentLoad = NoOfRows('RestConnectorMasterTable');
Let ValidateCount= If($(ValidateCount)=0,NoOfRows('RestConnectorMasterTable'),$(ValidateCount));
Let startAt = If($(FirstRun)=0,If($(ValidateCount)=$(CurrentLoad),$(Page),$(startAt)),$(startAt));
Let ValidateCount = NoOfRows('RestConnectorMasterTable');
Let FirstRun = 0;
NEXT startAt;