topic Re: Loop Issue in Connectivity & Data Prep

Loop Issue

tmumaw — Mon, 13 Dec 2021 21:44:07 GMT

Hi all,

I am having a problem looping through a table using the rest connector. I can get my first 5000 rows of data, but my nexturl variable is not getting populated. Does anyone see anything that would make it return a null value? Thanks

LET i = 1;

LET vURL = 'https://xyz.api.crm.dynamics.com/api/data/v9.0/accounts';

DO while LEN(vURL) > 0

CUSTOM CONNECT TO "Provider=QvRestConnector.exe;url=$(vURL);timeout=30;method=GET;sendExpect100Continue=True;autoDetectResponseType=true;checkResponseTypeOnTestConnection=true;keyGenerationStrategy=0;authSchema=anonymous;skipServerCertificateValidation=false;useCertificate=No;certificateStoreLocation=CurrentUser;certificateStorbeeName=My;queryHeaders=OData-MaxVersion%24.0%1OData-Version%24.0%1Authorization%2Bearer $(vToken);PaginationType=None;allowResponseHeaders=false;allowHttpsOnly=false;XUserId=ZCASARB;XPassword=GbTdcBA;";

RestConnectorMasterTable:
   SQL SELECT
      "@odata.context",
      "@odata.nextLink",
      "__KEY_root",
(SELECT
"@odata.etag",
   "accountid",
   "__FK_value"
   FROM "value" FK "__FK_value")
      FROM JSON (wrap on) "root" PK "__KEY_root";

[Accounts]:
    LOAD
      [@odata.etag],
   [accountid],
   [__FK_value] AS [__KEY_root]
    RESIDENT RestConnectorMasterTable
         WHERE NOT IsNull([__FK_value]);

//LET vURL = FieldValue(@odata.nextLink,1);
LET vURL = @odata.nextLink;

//DROP TABLE RestConnectorMasterTable;

LET i=i+1;

Loop

DROP TABLE RestConnectorMasterTable;

Re: Loop Issue

Gysbert_Wassenaar — Thu, 15 Nov 2018 18:13:32 GMT

Try LET vURL = Peek('@odata.nextLink',0,'RestConnectorMasterTable');

And modify your selections statement to include a WITH CONNECTION statement to dynamically change the url:

But it should be possible to use the standard NEXT URL pagination option of the rest connector:

https://help.qlik.com/en-US/connectors/Subsystems/REST_connector_help/Content/Connectors_REST/Create-REST-connection/Pagination-scenarios.htm

root\@odata.nextLink should be the path I think you need to specify.

Re: Loop Issue

tmumaw — Thu, 15 Nov 2018 18:09:10 GMT

Nope. Still only brought back first 5000 rows. Thanks

Re: Loop Issue

juraj_misina — Thu, 15 Nov 2018 19:08:21 GMT

Your connect string says "PaginationType=None", so I guess it simply loads first page of data only. See help page for details how to load paged data.

Juraj