Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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

1 Reply
Highlighted
Digital Support
Digital Support

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.