API pagination

[Anonymous]

Hi,

I'm having trouble setting up Qlik Sense Desktop to pull data in from our ticket system Freshdesk due to issues with pagination.

API call in the following structure:

https://domain.freshdesk.com/api/v2/tickets?page=x

There's also a URL returned in the response header in the following structure:

Link: <https://domain.freshdesk.com/api/v2/tickets?page=x>; rel="next"

What are the correct settings for pagination in the data connection? Is URL possible, or does it have to be custom script?

An important point: there's nothing in the headers that refers to total responses from the query, so if the API call actually returns 50,000 tickets I wouldn't know and I'd just have to loop through until I hit a page that has a JSON string length of < x. This is exactly what I do in a custom c# app that parses the JSON and loads an SQL server but I'm looking at Qlik as a solution that negates the need for a data warehouse.

Thanks all

[viegasss]

I have the same problem. Can anyone help?

[JonCarpenter]

Hi, 

Here is a fairly simple example of how to accomplish paging in a Qlik load script: 

SUB get_audit_reloads_ended

 SET vParams = 'limit=100&eventType=com.qlik.v1.integration-app.app-instance.ended&eventtype=com.qlik.v1.integration-app.app-instance.error';

// SET vParams = 'limit=100';

  DO

RestConnectorMasterTable:

SQL SELECT 

"__KEY_root",

(SELECT 

"id" AS "id_u0",

"contentType",

"eventId",

"eventTime",

"eventType",

"eventTypeVersion",

"source",

"tenantId" AS "tenantId_u0",

"userId",

"__KEY_data",

"__FK_data",

(SELECT 

"appId",

"blockReason",

"blocked",

"created",

"dataAppId",

"dataAppUniqueId",

"dataProjectId",

"deleteStatus",

"deleted",

"endTime",

"id",

"lastUpdated",

"readyToRun",

"runtimeCounter",

"runtimeError",

"runtimeMessage",

"runtimeState",

"runtimeSubState",

//"spaceId",

"startTime",

"startWhenReadyToRun",

"startedBy",

"tenantId",

"recovery",

"__KEY_data_u0",

"__FK_data_u0",

(SELECT 

"jobId",

"__FK_recovery"

FROM "recovery" FK "__FK_recovery"),

(SELECT 

"__FK_runtimeFullError"

FROM "runtimeFullError" FK "__FK_runtimeFullError"),

(SELECT 

"__FK_runtimeFullMessage"

FROM "runtimeFullMessage" FK "__FK_runtimeFullMessage"),

(SELECT 

"operation",

"__FK_startPayload"

FROM "startPayload" FK "__FK_startPayload")

FROM "data" PK "__KEY_data_u0" FK "__FK_data_u0"),

(SELECT 

"spaceId" AS "spaceId_u0",

"topLevelResourceId",

"__FK_extensions"

FROM "extensions" FK "__FK_extensions"),

(SELECT 

"__KEY_links",

"__FK_links",

(SELECT 

"Href",

"__FK_Self"

FROM "Self" FK "__FK_Self"),

(SELECT 

"href",

"__FK_self"

FROM "self" FK "__FK_self")

FROM "links" PK "__KEY_links" FK "__FK_links")

FROM "data" PK "__KEY_data" FK "__FK_data"),

(SELECT 

"Prev",

"__KEY_links_u0",

"__FK_links_u0",

(SELECT 

"Href" AS "Href_u0",

"__FK_Self_u0"

FROM "Self" FK "__FK_Self_u0"),

(SELECT 

"Href" AS "Href_u1",

"__FK_Next"

FROM "Next" FK "__FK_Next"),

(SELECT 

"href" AS "href_u0",

"__FK_self_u0"

FROM "self" FK "__FK_self_u0"),

(SELECT 

"href" AS "href_u1",

"__FK_next"

FROM "next" FK "__FK_next")

FROM "links" PK "__KEY_links_u0" FK "__FK_links_u0")

FROM JSON (wrap on) "root" PK "__KEY_root"

 WITH CONNECTION (  

      URL "https://$(vu_tenant_fqdn)/api/v1/audits?$(vParams)$(vNextURL)");

[Audits]:

LOAD [appId],

[blockReason],

[blocked],

[created],

[dataAppId],

[dataAppUniqueId],

[dataProjectId],

[deleteStatus],

[deleted],

[endTime],

[id],

[lastUpdated],

[readyToRun],

[runtimeCounter],

[runtimeError],

[runtimeMessage],

[runtimeState],

[runtimeSubState],

[startTime],

[startWhenReadyToRun],

[startedBy],

[tenantId],

[recovery],

[__KEY_data_u0],

[__FK_data_u0] AS [__KEY_data]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_data_u0]);

     [NextURL]:

        LOAD

            [Href_u1] AS NextURL

        RESIDENT RestConnectorMasterTable

        WHERE NOT IsNull([__FK_Next]);

        DROP TABLE RestConnectorMasterTable;

        LET vNextURL = TextBetween(Peek('NextURL',0,'NextURL'),'&next=',Null());

        IF SubStringCount('$(vNextURL)','&') >= 1 THEN

            LET vNextURL = TextBetween('$(vNextURL)',Null(),'&');

        END IF

        LET vNextURL = '&next=' & '$(vNextURL)';

        LET vNextURLRows = NoOfRows('NextURL');

        DROP TABLE NextURL;

        LET vCounter = $(vCounter) + 1;

   LOOP WHILE $(vNextURLRows)>0;

END SUB