OData connector paging (odata.nextLink) problem - can I use REST instead?

[alexis]

Hi all,

Using the URL, username and password provided by a 3rd party data provider I created an OData connector (OData_ACS) that enables me to access (using GetData - see below) the various tables ("Employee"in the case below) 

I have noticed that in the case of a few tables the connector was only returning (in this case) just 50 records and no more. When I queried this with the data provider they told me that they had done it deliberately as the specific tables are quite large. They pointed me to the XML for this data set that had the following at the end of the 50-record set

"odata.nextLink":"https://prod1wsi.exelsyslive.com/o/odata/Employee?skip=94244"

The data supplier added that both Excel and PowerBI automatically handle the multiple pages to return the entire data set but in Qlik Sense I cannot see how I can do this. I have spent hours looking at resources online to no avail. Can someone help.
I also created a REST connector to point to the same source but got the same result - can someone perhaps help me with the paging options in the REST connector if that is a better avenue to extract all the data.
Thanks in advance
#odata  #rest  #paging #odata.nextLink

[williejacobs]

Hi @alexis ,

The solution I have used for this was using a Rest connector.

You run your query with a do loop until a criteria is met, the next page is Null.

You can then pass the next page link as a variable and loop through the pages and concatenate them.

You will use the With Connection in your request. See snip of the script I used.

The variable, zURL, you will change to the next page URL.

FROM JSON (wrap on) "root" PK "__KEY_root"
WITH CONNECTION
(
URL "$(zURL)",
HTTPHEADER "Authorization" "Zoho-oauthtoken $(zToken)"
)
;

Hope this helps with your issue.

[alexis · Accepted solution]

Hi Willie,

Thanks for the response. In fact a few days ago I resolved the issue using a REST connector but I resolved the "pagination" in a different, (and I think) simpler way although parts of our code looks identical.

The REST connector configuration panel in Qlik has a "Pagination" section.

There are just 2 things to define, the "Pagination Type" and the "Next URL" path. Below you can see my entries that worked for me, and after that is the code that I used to extract the table in it's entirety.

Here is the code:

LIB CONNECT TO 'REST_Employee';

RestConnectorMasterTable:

SQL SELECT 

"__KEY_root",

(SELECT 

"EmployeeID",

.....,

"__FK_value"

FROM "value" FK "__FK_value")

FROM JSON (wrap on) "root" PK "__KEY_root";