Qlik Community

Ask a Question

Integration, Extension & APIs

Discussion board where members can learn more about Integration, Extensions and API’s for Qlik Sense.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
lewiszman
Contributor
Contributor

Please help with HubSpot API pagination settings

Hi,

I'm using Qlik Sense' REST API connector to pull data from HubSpot CRM. HubSpot documentation reads:

"Get all of the deals in a portal.  Returns a paginated set of deals.

In addition to the list of deals, each request will also return two values, offset and hasMore. If hasMore is true, you'll need to make another request, using the offset to get the next page of deal records." (from Get all deals‌ )

Default records returned is 100.

I need help configuring the 'pagination type' settings in Qlik Sense. Please help.

15 Replies
pankaj999
Contributor III
Contributor III

Could you let us know how you managed to load all contacts in qliksense?
adandreti
Contributor II
Contributor II

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;

fmarvnnt
Partner
Partner

Would you be so kind to share the solution?

 

Than you in advance,

 

FM

fmarvnnt
Partner
Partner

I imagin eyou find the solution.  Would you be so kind to share us the solution?

adandreti
Contributor II
Contributor II

Hello FM,

Did you try the proposed solution that I have posted above?

 

 

xy-siang
Partner
Partner

Hi Lewiszman

Sorry to bother you.

I have never use the Qliksense API connector to pull data from HubSport CRM before. Hence, would mind sharing a detailed guide or any advice  on how to setup the Qliksense API connector to pull data from HubSport CRM?

Then another thing is that do I need to know about Hubspot API for pulling data from HubSport CRM to Qliksense?

Appreciate if you can advice on the following above. Thank you.

Regards, 

XY Siang