Macro to repeat workflow for all fields in a text input row

Hello,

I am fairly new to Alteryx and I have a project to extract data from daily report files over a 6-month period. I was given a workflow which starts with a text input that has 1 row and 5 columns. The rest of the workflow repeats 5 times - one time for each column in the text input. The data in each column is a numerical representation of a date, which downstream in the process is used to determine the correct folder and file to find the necessary data in.

I have extensive experience in VBA so naturally I feel that there must be an easier way to do this than repeating the steps in workflow several times. Additionally, I am limited to running 5 days at a time right now.

I would like to set it up so that my text input has 1 column (called Date) and I can put 1 to many rows of data in this row, and have the workflow repeat until all rows in the text input are processed.

Additionally, since all data is output to the same Alteryx database file, I need each iteration of the workflow to complete before the next iteration starts.

I have been combing the internet to find a solution, even watching Alteryx training videos, and I am coming up empty (although I am learning more about macros )

Any help would be greatly appreciated!

Macros

Accepted answers

Yoshiro_Fujimori

Hi @TJ2468 ,

If you want to repeat the same workflow with different parameter, it is a case for Batch macro, as @MitchellBliesner mentioned.

Here is a simplified sample batch which calculates the date diff for each input parameter.

You may change the internal batch whatever you like. Good luck.

Input to Control Parameter ("Date2")

Input Data

Macro to add Date2 and calculate diff from Date

Output

The batch process is repeated for each Date2 as you see below.

Batch.yxzp

All comments

MitchellBliesner

You will need an Iterative macro where you feed the macro output into the macro input again. I'll like some resources where you can learn how do these.

https://www.thedataschool.com.au/patrick-breis/how-to-build-alteryx-iterative-macros/

https://www.youtube.com/watch?v=AByfTPqrUrQ

I used these to learn more about how to do it. I hope this helps. This is about all the help I can give without seeing the data and workflow.

TJ2468

Thanks for the link. I've read through it and I think my issue is different in that it seems like iterative macros are used to loop through the same records multiple times until the exit criteria is met. However, I simply need my workflow on the canvas to repeat itself for every row in my input text tool until all rows are evaluated.

Currently, the canvas contains the same workflow 5 times. Each one refers to a different column in the text input tool. So the top one looks at "Date", the next one looks at "Date2" and so on. Each subsequent one below the first one has a "Block Until Done" tools to prevent it from starting until the previous one completes.

I've attached a few screenshots to help try and describe what the current workflow does.

My hope is that I can change the input text tool to have one row with all of the dates listed in that row, and then have the workflow display on the canvas only one time instead of 5 times, and have the workflow repeat for every row of data in the input text tool. Again, I don't want it to start repeating the workflow until the previous iteration is completely done, since each iteration will be writing data to the same database file. Hopefully this helps explain it a little better. Thanks!

image.png

MitchellBliesner

Thank you for more information. I was wrong in my original answer. I believe you just need a batch macro. Enter the 5 dates into a single column of a text input. Then use this in a batch macro. Where you change the input based on a control. Or if the dates are the same space apart then you can enter one and do a formula to change through the dates. I will link to a blog where you can learn how to make one. Let me know if this works.

https://www.thedataschool.co.uk/marc-reid/creating-batch-macro-alteryx/

Yoshiro_Fujimori

Hi @TJ2468 ,

If you want to repeat the same workflow with different parameter, it is a case for Batch macro, as @MitchellBliesner mentioned.

Here is a simplified sample batch which calculates the date diff for each input parameter.

You may change the internal batch whatever you like. Good luck.

Input to Control Parameter ("Date2")

Input Data

Macro to add Date2 and calculate diff from Date

Output

The batch process is repeated for each Date2 as you see below.

Batch.yxzp

TJ2468

Thank you both for your help. I now have a batch macro set up. It ran through the first date field and then I got an error saying the database file could not be opened because it was being used by another process. I assume this was because it was still trying to write data to it from the first date (it was at 16K records when it I got the error). Is there anywhere I can put a Block Until Done so that it will write all of output for the first date input before it starts processing the second date input? Thanks again.

MitchellBliesner

It means that you have the document open somewhere else on your computer. You either have to close down wherever you have the data open other then Alteryx or what I do is (if I can) I make a copy of the data and use that in Alteryx so I can still have it open somewhere else for me to look at. I just struggled with that error message last week.

Hope this helps

TJ2468

Thanks again, that was not my exact problem but it did help me find the issue. I was able to successfully run my batch macro!

SyedHammadAli

Hi, can you send me a copy of your workflow if possible, please?

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

Hollingsworth 335

LanisC 335