Hello all,
I have recently connected QlikView to our HubSpot account using the REST connector for API.
While HubSpot has robust APIs in place for most objects, I have been unable to find a solution to get more records than the defined/default limit. This is generally 100/200 records only, depending on the object. I need to be able to retrieve all records for analysis.
I have tried the various Pagination options using the REST connection wizard in QlikView. However, while I can determine the 'Start' and 'Count' parameter names, there doesn't appear to be a 'Total records' path. As a result, Pagination doesn't seem to be an option. I have seen a similar issue logged by other users connecting to HubSpot in QlikSense.
The other option I have seen recommended is to use a loop in the Load script. Can any advice be provided on how to use this?
The current Rest Connector Master Table is producing the following (Deals API):
RestConnectorMasterTable:
SQL SELECT
"__KEY_root",
(SELECT
"id",
"createdAt",
"updatedAt",
"archived",
"__KEY_results",
"__FK_results",
(SELECT
"amount",
"closedate",
"createdate",
"dealname",
"dealstage",
"hs_lastmodifieddate",
"hs_object_id",
"pipeline",
"__FK_properties"
FROM "properties" FK "__FK_properties")
FROM "results" PK "__KEY_results" FK "__FK_results"),
(SELECT
"__KEY_paging",
"__FK_paging",
(SELECT
"after",
"link",
"__FK_next"
FROM "next" FK "__FK_next")
FROM "paging" PK "__KEY_paging" FK "__FK_paging")
FROM JSON (wrap on) "root" PK "__KEY_root";
How would I incorporate a loop into this to ensure all existing records are loaded?
Thanks very much in advance.
Dod
