Can you help simplify a workflow with 12 Record Selects?

[Philip]

I'm implementing the CDC Historical Limits Method for surveillance of disease detection (reference http://wwwnc.cdc.gov/eid/article/21/2/14-0098_article). I have a successful workflow, but there is one complicated step that I was wondering if could be simplified?

The step is in the Historical Limits Method macro, the second grouping (green) of the attached workflow.

To calculate the value of each month, I go through each month of the last year and take all values from previous months. Right now it involves 12 Select Records tools. It's not onerous, but if they choose to go back 24 months or 36 months, that's a lot of Record Selects to produce. Can anybody thing of a better way?

[KaneG · Accepted solution]

Hi Phillip,

I would turn everything between the sort and the Union into an Iterative Macro. I have attached what this would look like, with the iterative Macro attached. Run the iterative Macro with your dataset to have a look and make sure that it is the correct data that you are after.

In this case, we use the Iterative Macro design a little differently from normal as we want to feed all the data back in to the Iterative input, up until the number of Iterations hits the Number of Months that we want to go back.. 

Note: I'm not sure if you have implemented it here as the whole lot was packaged to upload, but you can put your supporting Macros in a sub-directory called 'supporting_macros' and then these will not appear on your toolstrip with the main Macro.

Kane

[Philip]

Thanks, Kane!

I looked at the iterative macro, but because it said it processes data one record at a time I thought I couldn't pass the whole data set. This is incredibly insightful!

[Philip]

When I place the iterative macro into the Historical Limits Method workflow, I receive the error The Iteration input "" is not present. Do you have any idea what's causing this and how to fix it? New workflow attached.

[RussellD]

Hi Philip,

This error is showing up because the Iteration Input/Output was not set within the Interface Designer. I'd take a look at this knowledge base article about building an Iterative Macro. http://community.alteryx.com/t5/Alteryx-Knowledge-Base/CS-Macro-Dev-Iterative-Macros/ta-p/10303. About halfway through is the info related to Interface Designer and those settings. @AlexKo takes you through building one from scratch and covers some tips and tricks that might help in building future macros.

Also the Iterative Macro Help gives the technical info on those options.

[Philip]

Thanks, Russell. That helped get everything sorted out. I learned a lot about how iterative macros work.

If anybody ever needs this, attached is the final workflow.