I need to implement a for loop.

[HarishKN]

Dear Friends,

I have built a workflow to mirror the pivot functionality of excel, however i have hit a road block as i need to repeat this action for every entry in a particular column of my data set.

Every time the work flow is successfully run i want the workflow to write the output into separate sheet of an excel work book.

Right now i have setup a manual filter and get the work flow to run once, what i need is to pass a unique value from a particular column in my dataset through the filter and then the output should be written to a different sheet of a workbook.

what i am thinking is that i should create a for loop for every entry in a field, i found some suggestions on the community to use a batch macro or an iterative macro however sadly i am not successful getting it work.

i have attaching the workflow for your reference. if needed. I am talking about the second filter i have used and from there on wards the following code needs to be repeated and write the results into a excel work sheet.

Regards

Harish KN

[jdunkerley79]

You can write to multiple sheets from a single output tool. 

You can make it decide which sheet to write to based on a field.

You need to have the filename and the sheet joined together.

I have put a quick demo together of what I mean.

[HarishKN]

Thank you jdunkerley79 but this is not my main concern.

Main concern is not writing to different worksheets, the issue which i need help is with passing a value through the filter and making the workflow to run on that value.

this value is also part of the data set, it is stored in a separate column, i need to find a way to pass this value one at a time, for every unique entry in this column. 

Regards

Harish KN

[Claje]

Hi Harish,

It looks like you meant to include some excel workbooks in the attached example.  Unfortunately, it doesn't appear that they saved with the workflow - so my response is based on what I think you're trying to do.

Basically you have a specific set of values, and each time one of these values appears, you want it to create a cross tab and output an excel file?

If yes, and assuming that you know/can bring in your unique values that you are looking for, I think you are looking to create a Batch Macro.  If you place everything starting with your second filter tool into a Batch Macro, and put a Macro Input tool before the filter, that should give you the baseline of what you need.  A Control Parameter can then be configured along with an Action tool to update the filter on each run.

If you've never built a Macro before they can be a little tricky.  I'd recommend looking here for more information: https://community.alteryx.com/t5/Alteryx-Knowledge-Base/CS-Macro-Dev-Reading-in-multiple-files-with-...

If you get stuck or have questions about Batch Macros, I'd be happy to try and help.

[jdunkerley79 · Accepted solution]

Sorry I had miss understood the issue.

As an alternative to @Claje batch suggestion, I might approach this as an iterative macro where I take the first UserID in the loop (use a sample tool to pick it) join it back to the input to just select those records and process those and then loop the unjoined set back to the beginning.

More than happy to put a sample together if you can share a sample of the data

[HarishKN]

Thank you for your suggestions, 

i was able to get this to work by creating an iterative macro. even though i think i can enhance the macro as believe the macro is repeating few of the things essential in terms of setting up the input files and other stuff.