Call an API in a loop using the latest returned max value of a parameter

[Izbiz]

Hi everyone,

So I have a we API that I am calling to first get all the historic data out. It limits me to 10000 rows at a time and i believe there's about 200000 rows of history. 

It's a typical url with input parameters. I start off a "dateNum" parameter at 0 on the first call and then repeatedly call it using the max latest figure returned. Unfortunately it doesn't go up in single integer step but you always use the max number returned in the next call.

I was manually inputting the new "dateNum" figure each time and running my workflow to append the new data to a table in SQL however this was getting quite messy .

I would like to find a way to run my work flow but have the new "dateNum" value selected based on the max number in the datenum column returned and then dynamically use that to run again etc until there's no more data to get out.

I thought I could write my end table to sql db and then have an offshoot to select the new parameters and re-run the flow. However I am not sure how to connect this all up and set it running with a log of how many calls were made - and if this is possible?

I have attached my current workflow (with my api credentials not in it so it wont run) just to show the process .

Any help will be much appreciated!

Thanks

[fmvizcaino]

Hi izbiz,

I have created a conceptual model that shows a way of doing what you need. To create the workflow, I've used the concept of an Iterative Macro.

https://community.alteryx.com/t5/Alteryx-Knowledge-Base/How-create-an-Iterative-Macro-The-Collatz-Co...

The idea behind is to iteratively get the max data from the API extration and run the workflow once again until there is no data available to extract.

Best,

Fernando Vizcaino

[Izbiz]

Hi Fernando,

Thank you, that's really helpful. I would really appreciate if you could check my updated macro

I have updated my workflow, trying to convert it to an iterative macro. I have the macro input, starting off the changing API call parameter, "dateNum" at 0.

Then the formatting part and two macro outputs, one which outputs the maximum returned dateNum from that API call and one to output the data ( this will be changed to a write to db tool)

I have set this running and it only calls the API once, returning the max no. records which is 10,000.

How does the macro know to update the macro input tool and run again and how will it know to keep going until no more data is returned?

Many thanks

Izzy

[fmvizcaino · Accepted solution]

Hi Izbiz,

The first thing you need to do is to configure Interface Designer as below. You can find it in 'View'. With that, the macro knows it should use the iterative output as input while there are still data in the output.

After that, you need to create a workflow that is going to call your macro in order to the iterative part to function. While you are inside your macro, the workflow will only run once and you can use it as a test.

I've configured the macro and the workflow that calls it. See if it works.

The workflow will run while there are data in the iterative output or while the max number of iterations is not hit.

Best,

Fernando Vizcaino

[Izbiz]

Hi,

That works! However it seems to be duplicated some rows and gets an error because annoyingly the number of columns changes during the API call. 

The iterative part appears to be working though! Thanks so much

[fmvizcaino]

Hi Izbiz, 

in order to eliminate this problem, you need to control the iterative output ensuring that the iterative output is in the same structure as the input. You can do that by adding a select tool before the output.

Best,

Fernando Vizcaino

[Izbiz]

Hi Fernando,

I am getting an error;

Error: ClipperAPImacro1 (5): Iteration #8: Tool #161: The field schema for the output "data output" changed between iterations.

I made sure to add null columns for those which appear during the api calls so the No. fields should be constant throughout.

Any idea what this refers to? It writes 7 iterations but then stops

Thanks!

[fmvizcaino]

Hi Izbiz,

Maybe in the 7th iteration, some new column appear in your API extraction. If you have inserted the select tool right before the iteration output, one thing that it's imperative for you to have it is the *Unknown field unchecked.

If it still doesn't work, please share your last version with me to check a few things. It would be nice to have a sample of your extraction.

Best,

Fernando V.

[Izbiz]

Yes, I'm happy that it gets to the 7th iteration.

I'm also replicating this macro for the same api call but different data values. and unfortunately the macro is not working right.

Iteration #96: Tool #2: Parse Error at char(84): Unknown variable "dateNum" (Expression #1)

The macro itself runs fine and outputs data for one call but when run it the calling workflow it fails. I can't spot the error in the first formula tool

I have attached the offending macro - if you can have a look would be greatly apreciated

izzy

[fmvizcaino · Accepted solution]

Hi Izbiz,

I've updated the macro and inserted a way of controlling the iteration stop. When the API extraction outputs less than 10000 rows, it means that there is no data left for a next iteration.

Best,

Fernando Vizcaino