Creating Flows with Loops

[7Skittles]

Hello,

I've been getting stuck with something and could use some help. 

What I'm trying to achieve is relatively simple - a flow to process identically formatted data into a set of identically formatted Excel files. The Render tool sounds like it would probably be perfect for this, but is not suitable because:

1. 3 datasets are filtered by a type-parameter and then each loaded into a dedicated sheet within a single, type-specific Excel file. There are multiple types and corresponding Excel-files, each with 3 sheets that I need to regularly update. 

2. I need to keep the content from the other sheets in those files intact and can't just recreate them. I was thinking about having 3 Render tools pointing at the same file, but the last tab to be written would then always be the only one left in the Excel files.

So far, I've only been able to get this to work by copy and pasting the 3 flows in containers for each type, which is messier and cruder than I would like. Regardless, it does allow me to use the Output Data tool instead of Render, which has the required option to only replace a specific sheet in the output file. 

Now, I think the best way to do this would be to have my flow iterate a list of type-parameters and execute these 3 dataflows, using the parameters to filter the data sources and also derive the output Excel file's name in the Output Data tools. 

Looking into the documentation, there's apparently support for parameters but no description how to actually create or use them, for example it mentions a "Flow view" from where you can create parameters but doesn't say how to access it and I can't find a way, either.

I've also checked out the forum, and some users talk about Macros, etc. but I can't find a good example that puts all these parts together into a coherent solution.

Is this kind of looped flow possible in Alteryx?

Thanks and kind regards!

[lwolfie]

If you don't want to use the render tool, you could try using blob tools.  

https://community.alteryx.com/t5/Engine-Works/Excel-Templates-with-Blob-Tools-and-Control-Containers...

[KGT · Accepted solution]

There's a couple of things that you are after here, and depending on what works best for your exact use case, you may have to try a couple things.

Firstly, Batch Macros may make this easier.

• A batch macro is like a For loop. (Whereas an iterative macro is more like a while loop)
• https://community.alteryx.com/t5/Alteryx-Community-Resources/Apps-and-Macros-Guide/ta-p/1104327
• https://community.alteryx.com/t5/Videos/Build-Your-First-Batch-Macro/td-p/52900

Next, the interaction writing to the same file using Render vs Output can be tricky. There has been questions about that, this year on the community and so you might want to check those out. And as @lwolfie mentioned, blobs might be an option as well, so that you can write the entire file. I don't do it, so not going to confuse you by posting links that may not apply.

My gut tells me that you may want to have the 3 flows separate and then a batch macro at the end of each of the 3, but you may be able to get more than that incorporated into the macro. The key with building a macro like this, is to get it working for one instance, and then productionise that. In your macro, you could even have the 3 flows possibly, and it selects which one to use... but it can get pretty involved rather quickly.

Don't be afraid of a messy solution that you can tidy up later.

[7Skittles · Accepted solution]

Thank you for your answer!

I agree, good shouldn't be the enemy of perfect, and have honestly stopped looking for the nicer approach and just deployed the "messy" solution, as it does work and only bothers me - but I thought I might just ask here and see if there's something obvious, that I'm missing.

Anyway, I'll look into those macro tutorials, as they do seem to offer useful functionality, that will likely help me with this and any other ETL-flows in future.

The link with the blob tools is also interesting, but the sheets I need to keep actually have user input on them, ugly as that is. So, I can't reproduce the outputs from a generic template file - unless I do some shenanigans to update/replace the template just before refreshing it, but that's still not a "clean" enough result for me to now go ahead and refactor this.

I do still wish we had a simple loop-container, but I've moved from bargaining straight into acceptance on this issue.

Thanks again and have a great day.

[7Skittles]

Thank you for this.

I've taken a look but as I've said in another reply, it won't help me in this case.

However, I do have flows with actual template files, so will keep this in mind.

Cheers!

[KGT]

I'm not sure I understand your issue then.

Just to make sure, here's a description of a couple of the items.

The blob option has methods where you import the existing file, update the part you need and then output it.

Workflow Constants can be set up in Workflow Properties (on the left of your screen by default after clicking on the background of the canvas) under the second tab (workflow).

A batch macro will allow you to repeat the flow with different options passed to the macro. (i.e filenames etc