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

Hubert Dudek · Accepted Answer · 2019-02-27T23:42:33.827

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:

edited Feb 27 '19 at 23:42

answered Feb 07 '19 at 18:39

Hubert Dudek

1,666
1
13
21

Thank you so much, this is exactly what I needed help with. – Pankaj Kaundal Feb 11 '19 at 12:15
Hey Hubert, One more problem i bumped into with this logic is that, the property ids are getting repeated . For e.g first iteration gets taggged with 1- 20 preperty ids and nxt 20 also gets tagged with 1-20 which should be 21-40 . Any idea ? – Pankaj Kaundal Feb 27 '19 at 12:17
This is id from qliksense not api? If api just drop field if qliksense In rest connector there is option "Key generation strategy". Probably you have it set to "Sequence ID" which is worst option in that case. Try other ones: "Current record", "Fully qualified record" or just "No keys" – Hubert Dudek Feb 27 '19 at 12:26
Thanks, It's from API, if I drop the fields how would I be able to manage the relationships? And where exactly to drop it, in sub or ? – Pankaj Kaundal Feb 27 '19 at 12:42
You can generate own key based on different fields using for example Autogenerate function. Which tables are you downloading from hubspot as you mention that you need to manage relationship? I have there account so can look later. – Hubert Dudek Feb 27 '19 at 12:48
I am downloading contacts table from hubspot. – Pankaj Kaundal Feb 27 '19 at 12:49
Thanks for assisting me on this Hubert, I'll wait for your comment. – Pankaj Kaundal Feb 27 '19 at 14:17
1

Yes "Fully qualified record" did a job. Test it. I've updated also the answer. – Hubert Dudek Feb 27 '19 at 23:43

Qliksense REST offset pagination using loop

1 Answers1

Linked