Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

twanqlik
New Contributor III

Qlikview - creating a loop for an API call

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?

7 Replies
qlikconsult
New Contributor III

Re: Qlikview - creating a loop for an API call

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

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

qlikconsult
New Contributor III

Re: Qlikview - creating a loop for an API call

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

qlikconsult
New Contributor III

Re: Qlikview - creating a loop for an API call

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

twanqlik
New Contributor III

Re: Qlikview - creating a loop for an API call

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.

qlikconsult
New Contributor III

Re: Qlikview - creating a loop for an API call

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

twanqlik
New Contributor III

Re: Qlikview - creating a loop for an API call

Thank you for your help and ideas!

qlikconsult
New Contributor III

Re: Qlikview - creating a loop for an API call

pleasure

Community Browser