topic Re: Qlikview - creating a loop for an API call in QlikView

Qlikview - creating a loop for an API call

twanqlik — Thu, 02 Nov 2017 15:51:35 GMT

I'm trying to create a loop for an API Call to Eloqua and i can use some help for this.

The Eloqua Rest API returns 1000 records per page.

The URL looks like below for the first 1000 records:

https://secure.p01.eloqua.com/api/REST/2.0/data/contacts?count=1000&page=1

In case there are more than 1000 records that you want to extract, you need to change the page number:

The below URL will return 1001-2000 records

https://secure.p01.eloqua.com/api/REST/2.0/data/contacts?count=1000&page=2

However, you don't know how many pages there are, hence, the API call needs to stop when the API call doesn't return any records.

Therefore, for the API call i need to create a loop where the pagenumber is 'x', and the call needs to stop when the call returns with 0 records. Till then the API call need to extract data and place it in a table.

I simply don't know how to write this, can someone help me with this?

Re: Qlikview - creating a loop for an API call

qlikconsult — Fri, 03 Nov 2017 21:17:16 GMT

Hi,

I recently had the same challenge, but in my case it was for airTable.

I suggest you have a look at the "offset" parameter in the query string as referred to in the link:

https://docs.oracle.com/cloud/latest/marketingcs_gs/OMCAB/Developers/BulkAPI/Tutorials/Retrieving_Large_Volumes.htm

The offset value should be returned on the GET call and you can use this in your loop to return the next page's records. The trick you need to apply in your loop is that when the offset value is null, it means that there are no more pages after the current load, which means you need to end the loop after that iteration,

Have a look at the example I'm pasting, maybe this can be off some help for you to apply the same logic to your script, you will see that after every loop I write out a qvd with the loop iteration (variable i) as the suffix. After you finish the loop process, you simply LOAD * all the qvd's to get all the data in the same table. Note that I never do more than 11 iterations, but you can change the for i = 0 to 100000000 if you need to

Hope this helped a bit to steer you in the right direction

Jan-Hendrik

Re: Qlikview - creating a loop for an API call

qlikconsult — Fri, 03 Nov 2017 21:27:37 GMT

From the documentation I see that Eloqua's offset might work different, using for example 1000 to indicate retrieving records 1000 to 1999, maybe you can change the logic a bit to say that if no data is retrieved or if the actual nr of records retrieved is less than the limit parameter nr of records, then exit the loop

Re: Qlikview - creating a loop for an API call

qlikconsult — Fri, 03 Nov 2017 21:45:57 GMT

to check how many rows are in the previously loaded table, use the NoOfRows function

http://help.qlik.com/en-US/qlikview/12.1/Subsystems/Client/Content/Scripting/TableFunctions/NoOfRows.htm

Re: Qlikview - creating a loop for an API call

twanqlik — Mon, 06 Nov 2017 08:59:42 GMT

I also think that this is the way that you should write the logic but i'm no expert in that and therefore i'm struggling with it

What I currently did:

1) One call (count =1) to retrieve the Total amount of records

2) Use this total value to calculate how many pages i need to extract.

3) Create a second API call and use the number of pages in a For Loop to extract the data.

This works but is not ideal because i need to make one extra API call for it.

-------------

API connection:

RestConnectorMasterTable:

SQL SELECT

"total"

FROM JSON (wrap on) "root";

[Total_Records]:

LOAD

[total],

ceil(total/$(Pagesize)) as Pages

RESIDENT RestConnectorMasterTable;

LET Pages = peek('Pages');

Start For Loop:

for a=1 to $(Pages)

RestConnectorMasterTable:

SQL SELECT

.....

Etc.

Re: Qlikview - creating a loop for an API call

qlikconsult — Mon, 06 Nov 2017 10:15:39 GMT

make sense what you are doing,

you can drop the tables (as shown in bold below), so that you are only left with the populated variable which you need for your loop api call - that's the only other contrinbution I can make

[Total_Records]:

LOAD

[total],

ceil(total/$(Pagesize)) as Pages

RESIDENT RestConnectorMasterTable;

drop table RestConnectorMasterTable;

LET Pages = peek('Pages');

drop table [Total_Records];

Re: Qlikview - creating a loop for an API call

twanqlik — Wed, 08 Nov 2017 14:53:01 GMT

Thank you for your help and ideas!

Re: Qlikview - creating a loop for an API call

qlikconsult — Wed, 08 Nov 2017 14:56:34 GMT

pleasure

Re: Qlikview - creating a loop for an API call

marksouzacosta — Sat, 15 Jun 2024 22:16:28 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