Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
sivaramr
New 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
Support
Support

Re: Rest connector using next url & next token pagination

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 don't forget to use the "Accept as Solution" button on any posts that helped you resolve your problem or question.