How to build iterative process

Question

We have a financial model for our business cases that we do. Currently we have multiple scenarios. Instead of inputting the assumptions one by one in a financial model and copy pasting its output to a summary sheet, I am looking to use alteryx to take all the scenario inputs currently on excel in a single sheet, run through the financial model, export the data in a sheet and then go to the next scenario.

Currently, I have a very manual workflow. Each time I add 'phase' in the excel file as a tab, I have to almost copy paste the workflow with slight changes. This process doesn't even summarize the metrics as I would like do in a single sheet.

In summary,

Excel file with scenario tabs -> alteryx picks assumptions -> runs through the model -> exports data to a single file-> picks the next scenario until complete.

atleryx workflow.PNG

MattBSlalom · Answer

Oh, ok, so the only differences are the Sheet name on the Input and the File Name on the Output?

In that case, the Batch Macro I described would be the way to go.  The macro will handle the activities you'd perform against a single sheet, and it will be called by a parent workflow that passes in the list of sheets to take action on.  In my sample macro I've just got a placeholder Container with a dummy Filter tool where you'd replace with all your actual logic.

Simple parent workflow to gather sheet names and pass into batch macro:

Sample Batch macro to process 1 sheet at a time:

ProcessPhases.yxmd

ProcessExcelSheets.yxmc

smrizvi24 · Answer

Hey.. thank you for the quick response.

by slight changes I refer to the tab name and model name. for example, if you look at the start of the workflow, the top one has a input tool that goes to a file with the name 'Phase 29' and ends up in 'Phase 29' model. the one at the bottom takes 'Phase 30' tab and ends up in 'Phase 30' model .

So each time I add a scenario, I have add this input data tool, connect it to the right phase and then make another model in excel name it let says Phase 31 model. but essentially they are going to the same file for inputs and the output file is the same financial model but as a different file name.

MattBSlalom · Answer

From the screenshot it's difficult to identify what are the "slight changes" you're needing to make for each "phase".  In general, your situation sounds like a fit for a Batch Macro.  The Control Parameter would be your "phase" which would drive the source Excel sheet name and whatever other "slight changes" are needed on other tools.  This setup will effective process 1 sheet through the macro, then update all the tool configurations attached to the Control Parameter before processing the next sheet, and so on.

If you can post a mocked up sample of your "slight changes" I can provide an example of the above Batch Macro scenario.