topic Re: Loop using Rest API in QlikView

Loop using Rest API

tmumaw — Sat, 16 Nov 2024 05:07:48 GMT

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;

Re: Loop using Rest API

kjhertz — Tue, 27 Nov 2018 13:45:08 GMT

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 🙂

Re: Loop using Rest API

tmumaw — Tue, 27 Nov 2018 16:23:57 GMT

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

Re: Loop using Rest API

tmumaw — Tue, 27 Nov 2018 20:19:12 GMT

nextURL is not getting populated.

Re: Loop using Rest API

marksouzacosta — Sat, 15 Jun 2024 22:08:54 GMT

As an addition to this topic, I'm excited to share my latest tutorial on integrating Asana with Qlik Cloud using REST APIs!

In this step-by-step guide, you'll learn:
- How to use Asana REST APIs with Postman
- Creating REST Data Connections in Qlik Cloud
- Extracting data from Asana and saving as Parquet files
- Enhancing connections with custom parameters and pagination

Check out the full video on YouTube:
Integrate Asana with Qlik Cloud Using REST APIs: A Step-by-Step Guide
https://youtu.be/lFwar30BNkQ?si=UZoM_x9L5amdJSnf

Regards,

Mark Costa