Iterative Macro for Download Tool and SFTP errors

[jschroeder]

I get intermittent errors when trying to access a SFTP site and wondering if a iterative macro would work to loop the event until successful download. 

[BrandonB]

You could set up the workflow to download the list of file paths and then do a join against the list of file paths that you already have downloaded. Then whatever comes out of the L on the join would be the list of files on the SFTP that haven't yet been downloaded which you can feed into a subsequent download tool. The next time the workflow runs, the successfully downloaded files would make it through to the inner join and therefore would not be downloaded again. Would this work for your situation?

[danilang · Accepted solution]

Hi @jschroeder 

That's a perfect use case for an iterative macro.  If you get an invalid/error result, send the URL out through the Iterative Output so that it loops back around. Make sure to clean out all extra fields until the schema of iterative output matches that of the Iterative input.   If you get a correct response, send it out the other output and send 0 rows out the iterative output. 

You might want to set the iteration count to something smaller than the default(100) as well.  If the SFTP site is actually down, you don't want to try and time out 100 times, before returning 

Dan  

[jschroeder]

The iterative macro worked perfectly.  Thank you for confirming that this is the appropriate method for this scenario.

John 

[karan1107]

Can you give an example workflow?

[BrandonB]

Hi @karan1107 

We have a training video located here on the creation of iterative macros: https://community.alteryx.com/t5/Interactive-Lessons/Creating-an-Iterative-Macro/ta-p/657925 

[TheOC]

hey @karan1107 

Also check this community post:
https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Getting-Started-with-Iterative-Macr...

Cheers,
TheOC

[karan1107]

Hi, Thanks for the reply. I do have a basic idea about iterative macros. the task is that I give a URL which contains date(which is entered through a text input). Now, what I want is that if the file is not available for that date, the macro pushes the date 1 day back and try again till it finds a file. Example : I entered 13th april, the download tool does not find a file for 13th so it gives an error "File not found". If i enter 12th, it does find a file and extract data from it. I want the macro to change the date. The file has date separated by underscores(xxxx_4_12_2021.txt)

[TheOC]

hi @karan1107 

You could have it as part of the macro that it only has 1 iteration, but that iteration is only increased if the error "file not found" is not given by the url.

This would be my suggestion for this task, you could generate the url and dates with the generate rows tool.
https://help.alteryx.com/current/designer/generate-rows-tool

Here you may start with a list of 100 dates for example, all generated by the generate rows tool, with each -1 day from the last, and it keeps iterating until there is a result.

Hope this helps!
TheOC

[karan1107]

How to increase the iteration only on the basis of error?