Loop Issue

[tmumaw]

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;

[Gysbert_Wassenaar]

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

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

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"
WITH CONNECTION (URL '$(vURL)');

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...

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

[tmumaw]

Nope.  Still only brought back first 5000 rows.  Thanks

[juraj_misina]

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