Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
sivaramr
Contributor II
Contributor II

Rest connector using next url & next token pagination

I am working on Rest connector using Hubspot API. API is returning 20 records on 1st call and remaining records can be accessed using pagination. API supports both token and next url.

Below is the API  paging screen,

hubspot-api.png

I tried using next url in Qlikview to access the paginated pages, but it is still downloading the first 20 records and not downloading the data from paginated pages. I don't know whether the next url path i am using is correct. Please verify and correct me if i am wrong. 

Below is the script in script editor screen,

 

 

CUSTOM CONNECT TO "Provider=QvRestConnector.exe;url=<a href="https://api.hubapi.com/form-integrations/v1/submissions/forms/b00d8ae5-cffd-45cc-b085-6b52a2696b95?hapikey%28c6e0be0-6aca-49fc-8008-e52210d2635b;timeout=30;method=GET;autoDetectResponseType=true;keyGenerationStrategy=0;useWindowsAuthentication=false;useCertificate=No;certificateStoreLocation=CurrentUser;certificateStoreName=My;PaginationType=NextUrl;NextUrlFieldPath=root/paging/next;IsNextUrlFieldPathHeader=0;XUserId=YPFMTBB;XPassword=GSAFFNA" target="_blank">https://api.hubapi.com/form-integrations/v1/submissions/forms/b00d8ae5-cffd-45cc-b085-6b52a2696b95?hapikey%28c6e0be0-6aca-49fc-8008-e52210d2635b;timeout=30;method=GET;autoDetectResponseType=true;keyGenerationStrategy=0;useWindowsAuthentication=false;useCertificate=No;certificateStoreLocation=CurrentUser;certificateStoreName=My;PaginationType=NextUrl;NextUrlFieldPath=root/paging/next;IsNextUrlFieldPathHeader=0;XUserId=YPFMTBB;XPassword=GSAFFNA</a>;";

RestConnectorMasterTable:
SQL SELECT 
	"__KEY_root",
	(SELECT 
		"submittedAt",
		"pageUrl",
		"__KEY_results",
		"__FK_results",
		(SELECT 
			"name",
			"value",
			"__FK_values"
		FROM "values" FK "__FK_values")
	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";

[values]:
LOAD	[name],
	[value],
	[__FK_values] AS [__KEY_results]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_values]);

[results]:
LOAD	[submittedAt],
	[pageUrl],
	[__KEY_results],
	[__FK_results] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_results]);

[next]:
LOAD	[after],
	[link],
	[__FK_next] AS [__KEY_paging]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_next]);

[paging]:
LOAD	[__KEY_paging],
	[__FK_paging] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_paging]);

[root]:
LOAD	[__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__KEY_root]);

DROP TABLE RestConnectorMasterTable;

 

 

Below is the screenshot of Qlikview pagination setting,

hubspot-img.png

Please help me in downloading the data from paginated pages using next url.

Thank you

Labels (5)
1 Reply
Brett_Bleess
Former Employee
Former Employee

See the somewhat duplicate post for further information: https://community.qlik.com/t5/QlikView-Scripting/Rest-connector-using-next-url-pagination-not-workin...

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.