Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
tmumaw
Valued Contributor

Loop using Rest API

Good Morning All,

I have an issue while looping through my Accounts table.  It only has 30K rows of data, but does not know when to stop looping.  I thought checking for the length of the variable being returned would work, but it appears it doesn't.  Any ideas?

Thanks......

 

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

LET i = 1;

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;";

DO while LEN('$(vURL)') > 0 
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)");


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

STORE * FROM [RestConnectorMasterTable] into qvd\Accounts365.QVD
;

 
[root]:
LOAD [@odata.context],
[__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__KEY_root]);

Let vURL = FieldValue('@odata.nextLink',1);

LET i=i+1;

Loop

DROP TABLE RestConnectorMasterTable;

Labels (1)
3 Replies
Partner
Partner

Re: Loop using Rest API

It seems you are autoconcatenating your RestConnectorMasterTable with each DO WHILE loop. This should mean the line "Let vURL = FieldValue('@odata.nextLink',1)" will always return the first URL loaded since FieldValue returns the value by load order (see Help for this function).

Try adding "Trace $(vURL)" after you update the variable to see if the variable vURL is set to what you would expect.

If you are constantly using the first loaded URL try using the Peek() function to return the last URL read.

Also beware of spaces within the vURL string. I would also adjust the exit condition to LEN(TRIM('$(vURL)'))>0 just to be sure 🙂

tmumaw
Valued Contributor

Re: Loop using Rest API

I'm a rookie when it comes to using the REST API.  How can I get the next url in vurl?  CRM365 only allows 5000 rows per call.

Thanks

tmumaw
Valued Contributor

Re: Loop using Rest API

nextURL is not getting populated.