Hi all, I was able to pull all Hubspot deals with some adjustments to the beginning and bottom of the "insert script" sql.
Firstly it is important to note that offset does not return a timestamp of the last request it is more so an access token that needs to be stored and used in the next API call to get the next bunch of records. The process I used to fetch all deals was to set up my rest connector connection without any pagination options selected, from there I used the select data -> insert script options to drop the SQL and Qlik table loads into my data load editor. One thing I noticed was that the select data option wasn't pulling in the hasMore or offset fields so I wrapped the initial sql query with another select statement to grab these fields (see bolded code below for SQL additions). Once I had my offset and hasMore fields I wrapped the sql in a do while loop and created variables such as vOff, vUrl and vHas to change at the end of every API get request looping through each offset until the hasMore field=False. Below is my data load without the list of Qlik load tables.
let vURL='https://api.hubapi.com/deals/v1/deal/paged?hapikey=*****&includeAssociations=true&limit=250&properti...';
let vOffset='';
let vHas='True';
do while vHas='True';
RestConnectorMasterTable:
SQL SELECT
"hasMore",
"offset",
"__KEY_root",
(SELECT
"portalId",
"dealId",
"isDeleted",
"__KEY_deals",
"__FK_deals",
(SELECT
"__KEY_associations",
"__FK_associations",
(SELECT
"@Value",
"__FK_associatedVids"
FROM "associatedVids" FK "__FK_associatedVids" ArrayValueAlias "@Value"),
(SELECT
"@Value" AS "@Value_u0",
"__FK_associatedCompanyIds"
FROM "associatedCompanyIds" FK "__FK_associatedCompanyIds" ArrayValueAlias "@Value_u0"),
(SELECT
"@Value" AS "@Value_u1",
"__FK_associatedDealIds"
FROM "associatedDealIds" FK "__FK_associatedDealIds" ArrayValueAlias "@Value_u1"),
(SELECT
"@Value" AS "@Value_u2",
"__FK_associatedTicketIds"
FROM "associatedTicketIds" FK "__FK_associatedTicketIds" ArrayValueAlias "@Value_u2")
FROM "associations" PK "__KEY_associations" FK "__FK_associations"),
(SELECT
"__KEY_properties",
"__FK_properties",
(SELECT
"value" AS "value_u0",
"timestamp" AS "timestamp_u0",
"source" AS "source_u0",
"sourceId" AS "sourceId_u0",
"__KEY_dealstage",
"__FK_dealstage",
(SELECT
"name",
"value",
"timestamp",
"sourceId",
"source",
"__KEY_versions",
"__FK_versions",
(SELECT
"@Value" AS "@Value_u3",
"__FK_sourceVid"
FROM "sourceVid" FK "__FK_sourceVid" ArrayValueAlias "@Value_u3")
FROM "versions" PK "__KEY_versions" FK "__FK_versions")
FROM "dealstage" PK "__KEY_dealstage" FK "__FK_dealstage"),
(SELECT
"value" AS "value_u2",
"timestamp" AS "timestamp_u1",
"source" AS "source_u2",
"sourceId" AS "sourceId_u1",
"__KEY_hs_object_id",
"__FK_hs_object_id",
(SELECT
"name" AS "name_u0",
"value" AS "value_u1",
"source" AS "source_u1",
"__KEY_versions_u0",
"__FK_versions_u0",
(SELECT
"@Value" AS "@Value_u4",
"__FK_sourceVid_u0"
FROM "sourceVid" FK "__FK_sourceVid_u0" ArrayValueAlias "@Value_u4")
FROM "versions" PK "__KEY_versions_u0" FK "__FK_versions_u0")
FROM "hs_object_id" PK "__KEY_hs_object_id" FK "__FK_hs_object_id")
FROM "properties" PK "__KEY_properties" FK "__FK_properties"),
(SELECT
"@Value" AS "@Value_u5",
"__FK_imports"
FROM "imports" FK "__FK_imports" ArrayValueAlias "@Value_u5"),
(SELECT
"@Value" AS "@Value_u6",
"__FK_stateChanges"
FROM "stateChanges" FK "__FK_stateChanges" ArrayValueAlias "@Value_u6")
FROM "deals" PK "__KEY_deals" FK "__FK_deals")
From JSON (wrap on) "root" PK "__KEY_root"
WITH CONNECTION (url "$(vURL)&$(vOffset)" );
...
~~~Qlik Load Tables~~~~
...
Let vOff = Peek('offset', -1, 'root'); //store the latest offset value in a variable
let vOffset=''; //reset the vOffset for next batch
let vOffset='&offset=' & '$(vOff)'; //append the latest
Let vHas = Peek('hasMore', -1, 'root');
DROP TABLE RestConnectorMasterTable;
loop;
