Macro to Process One week at a time and use the results in the next iteration

[3a_aa]

Hi Guys,

I've built a predictive algorithm which predicts missing data points. It's based off an average of the previous 10 weeks of data.

I need to run this on a file containing 5 years of history and fill in the missing points from 5 years ago.

Since every week, the calculation to predict the missing values is reliant upon historical data, I need to run my workflow for one week at a time, update all empty records for that week with the predicted value, then run the next week (with the previous weeks missing values filled in) and so on.

I've read up on Batch Macros but the complexity here is that I need to update the main source file after each iteration.

Any ideas would be appreciated

Thanks

[clant]

Hello,

I believe you would need to use a Iterative macro to achieve this. Here is a guide on creating one, https://community.alteryx.com/t5/Alteryx-Knowledge-Base/How-create-an-Iterative-Macro-The-Collatz-Co....

If you want to see some good examples of iterative macros there was a great weekly challenge you can see here.

I hope this helps

cheers

chris

[3a_aa]

Thank you for the reply.

Can I use an iterative macro to loop one week at a time or do iterative macros only do one row at a time?

I have a million rows of data so passing through one row at a time would take too long.

Thanks

[clant]

Hi,

You can do this one week at a time, i would just set a column to flag when you have updated a week and then use a filter at the start of your iterative macro to just select the week you wish to generate data for.

cheers

chris

[bbak]

What you could do is have a filter in your macro that filters on the week field in your data. If you don't already have a week field, I would create one with the first day in the week. 

Then have a control parameter and action tool feeding into this filter where [Week] = "2018-08-20"

Feeding into the macro you could have your list of week start dates that are passed through to the filter for each run. You can customize each step of the workflow to utilize this variable that is passed in to filter and analyze as needed. 

[3a_aa]

Thank you that's really useful.

I have a field called "week". I can filter my workflow on just that week.

Are you saying I need to have a file, which is a list of every week.

I feed this file into the macro (which is my workflow) and it will process in the macro one week at a time?

How would I need to change my workflow to turn it into an iterative macro that could work like this?

[bbak]

That is one way of handling this, yes. You could either have a file which is a list of every week, or you could use the input from your main file and summarize the week list by doing a group by week to then flow into your macro. 

There are a few different ways of handling this, but what I am showing is that by feeding in a list of the weeks through the control parameter, the macro will run once for each value that is passed through. You update the values where needed within the macro using the control parameter and update value. Then the output will be the resulting data stacked for each run. 

[3a_aa]

This makes a lot of sense

My only question is i need the output of each iteration to be the main file, to be used for the next iteration.

Would i have the main file being loaded/saved to within the macro? If so, where would my macro input/output go?

[bbak · Accepted solution]

I believe that every time the macro runs it re-reads the input files within the macro so if you write back to the file within the macro then every iteration it will pull the updated version. In this case you wouldn't actually need a macro output (only the file output within the macro). You would only need your control parameter to read in the date to update the tools needed. 

As you play with this, definitely make a copy of your original test file so that you can observe how it gets overwritten!

[3a_aa]

Thats interesting. Does the macro not need a designated macro output tool in order to run?

I'll play around with this thank you!