Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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:
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
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
to check how many rows are in the previously loaded table, use the NoOfRows function
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.
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];
Thank you for your help and ideas!
pleasure
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