Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Server Discussions

Find answers, ask questions, and share expertise about Alteryx Server.

API -Iterate every 1000 records and Automate

dbhamber
6 - Meteoroid

Hi,

Please can someone help me.

I am trying to create an API get command to Iterate every 1000 records from a SAP database.

My Get command has 3 tables in the URL:

These tables are related to each other with an ID number, the ID is unique and looks at this for the next table

                                                tableC – ID number

                                                tableM – ID number

                                                tableS – ID number

 

I want to automate this where tableC  loops (looks at all IDS matches this to the next tableM and then matches to tableS) this continues until all records have been linked and collected.

The maximum of records is 1000 per GET call.

 

My URL looks like this XML and not JSON - the xml is set to auto detect child and returns child entries.

 

https://api.company.com/server/tableC/RKQ3UZs2ss41/tableM/4ZJ6I7W-Pg01/tableS/FIkeC4f0XLA1

 

The connection works is a manually add the IDs which only retrieves information in bits.

I want to automate this, any advice or guidance would be appreciated.

 

dbhamber_0-1625575770068.png

 

2 REPLIES 2
RishiK
Alteryx
Alteryx

@dbhamber I am wondering if you can wrap the API call within a macro, and leverage pagination in the workflow.  This will allow you to read in your data in batchs and iterations (if done as a batch macro or as an iterative macro) and ensure you read all of your data in too.  Here is a useful article on API calls and Pagination:

 

https://www.thedataschool.co.uk/laine-caruzca/api-pagination-in-alteryx#:~:text=This%20is%20especial....

 

This article may be helpful for you too:

 

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Iterative-macro-for-API-pagination-ret...

 

 

ChBurger
6 - Meteoroid

Hey dbhamber,

 

I would recommend utilizing the skip token within the data if available. I integrate with a SuccessFactors oData API and I was able to create an Iterative macro, which will run one or many times and process the 1000 records returned at each iteration. It then checks for the skip token and if found processes the next feed of data using the Engine.IterationNumber to keep track of the iterations (pages of data) we've processed. Once there is no skip token it returns the entirety of the dataset.

ChBurger_1-1677019827889.png

 

I would recommend getting all table data, then merging the data later using a join tool. Remember, if you need all data from one side of the join and the data joined in you must union those two tracks or you will lose data you want/need.

 

Cheers,

Chad