qlikview - Qliksense REST offset pagination using loop

Question

I need to paginate through all of the records from Hubspot API and I am getting stuck at offset pagination loop. According to the Hubspot's API documentation, there is no "total records" path available in the response but instead "has-more" filed tells us if there are any more records we can pull from this portal. two parameters that can be used for pagination are

vidOffset & has-more

here's what qliksense script looks like for custom pagination via rest connector.

LIB CONNECT TO 'HubSpot ';
// Action required: Implement the logic to retrieve the total records from the REST source and assign to the 'total' local variable.
Let total = 0;

Let totalfetched = 0;
Let startAt = 0;
Let pageSize = 100;

for startAt = 0 to total step pageSize
RestConnectorMasterTable:
SQL SELECT 
    "has-more",
    "vid-offset",
    "__KEY_root",
    (SELECT 
        "addedAt",
        "vid" AS "vid_u0",
        "canonical-vid",
        "portal-id",
        "is-contact",
        "profile-token",
        "profile-url",
        "__KEY_contacts",
        "__FK_contacts",
        (SELECT 
            "@Value",
            "__FK_merged-vids"
        FROM "merged-vids" FK "__FK_merged-vids" ArrayValueAlias "@Value"),
        (SELECT 
            "__KEY_properties",
            "__FK_properties",
            (SELECT 
                "value",
                "__FK_firstname"
            FROM "firstname" FK "__FK_firstname"),
            (SELECT 
                "value" AS "value_u0",
                "__FK_lastmodifieddate"
            FROM "lastmodifieddate" FK "__FK_lastmodifieddate"),
            (SELECT 
                "value" AS "value_u1",
                "__FK_company"
            FROM "company" FK "__FK_company"),
            (SELECT 
                "value" AS "value_u2",
                "__FK_lastname"
            FROM "lastname" FK "__FK_lastname")
        FROM "properties" PK "__KEY_properties" FK "__FK_properties"),
        (SELECT 
            "@Value" AS "@Value_u0",
            "__FK_form-submissions"
        FROM "form-submissions" FK "__FK_form-submissions" ArrayValueAlias "@Value_u0"),
        (SELECT 
            "vid",
            "saved-at-timestamp",
            "deleted-changed-timestamp",
            "__KEY_identity-profiles",
            "__FK_identity-profiles",
            (SELECT 
                "type",
                "value" AS "value_u3",
                "timestamp",
                "is-primary",
                "__FK_identities"
            FROM "identities" FK "__FK_identities")
        FROM "identity-profiles" PK "__KEY_identity-profiles" FK "__FK_identity-profiles"),
        (SELECT 
            "@Value" AS "@Value_u1",
            "__FK_merge-audits"
        FROM "merge-audits" FK "__FK_merge-audits" ArrayValueAlias "@Value_u1")
    FROM "contacts" PK "__KEY_contacts" FK "__FK_contacts")
FROM JSON (wrap on) "root" PK "__KEY_root"
WITH CONNECTION(Url "https://api.hubapi.com/contacts/v1/lists/all/contacts/all");
// Action required: change URL included in 'WITH CONNECTION' as needed to support pagination for the REST source. 
// Please see the documentation for "Loading paged data."

NEXT startAt;

Need to understand how to set this up as per my API paramteres i.e. offset & hasmore property. How do i loop through all of the vidoffset values so that i can get all of the records until has-more becomes false?

Here's my json response

Answer 1

Please try recursive call to do you need to put your call in subroutine than check for has_more and if it is equal to True call subroutine again. Also Url parameter have to be updated every time with new vid-offset value. Here is example (tested it is working):

SUB getOnePage(vOffset)

  LIB CONNECT TO [hubspot_api];

  RestConnectorMasterTable:
  SQL SELECT 
  (...)
  FROM JSON (wrap on) "root" PK "__KEY_root"
  WITH CONNECTION (Url "https://api.hubapi.com/contacts/v1/lists/all/contacts/all/?hapikey=YOURKEY=$(vOffset)");

  LET vHasMore = Peek('has-more',-1);
  LET vVidOffset = Peek('vid-offset',-1);

  DROP Table root;

  IF vHasMore = 'True' then

    CALL getOnePage($(vVidOffset));

  EndIf

EndSub

Because of repeated keys in every CALL we need to change settings in REST connector as follow: