Output multiple flows to one file with dynamic name?

[Baz123]

Hi,

I have a workflow that splits off to 4 separate flows. The first workflow picks up an excel template and copies it to an output folder and pastes the output data into a named tab, then finally renames the template to include the current date in the format of YYYY.MM_<Filename>.xlsx

What I am trying to do is have the final three workflows then point to this output file with the dynamic name and paste the data where it needs to be.

Everything works if I have the output path with the actual current filename - 2023.04_<Filename>.xlsx

What I need help with - how do I change this output so that it works with a wildcard filename?

If I change the output path to *<Filename>.xlsx|||<Sheet>$<Range> then Alteryx is throwing an error of 'Unable to Open archive for zipping....the filename, directory name, or volume label syntax is incorrect.

I have tried the solution in the subsequent flows to include a formula tool to include the path in a column, use that as the output etc but that creates other issues.

Thanks.

[Raj]

Here's an example workflow that demonstrates how this can be done:

First, in your initial workflow that generates the output file, use a "Formula" tool to create a new field that concatenates the current date with the filename. For example, you could use the formula:

"2019.04_+ [Filename] +.xlsx"

Use a "Dynamic Rename" tool to rename the output file to the generated filename.

Use a "Formula" tool to create a new field that stores the generated filename as a variable. For example, you could use the formula:

"C:\OutputFolder\+ [GeneratedFilename]"

Connect this workflow to the subsequent workflows that need to reference the output file.

In the subsequent workflows, use a "Dynamic Input" tool to specify the file path and filename as a variable. For example, you could use the following format:

"C:\OutputFolder\+ [GeneratedFilename] + |Sheet1$A1:B10"

Configure the "Dynamic Input" tool to read the data from the specified file and sheet/range.

hope this helps

[binuacs]

@Baz123 You said you have the same workflow with four separate flows say A, B, C, and D, and A generate an output file and save it in the directory with the name YYYMM_filename.xlsx, then this output file is used by the other flows. My question why do you want the other flows to read directly from the file YYYMM_filename.xlsx instead why don't you read it from Workflow A. I mean without reading from the Excel output file read it from the first flow. 

[Baz123]

Hi @Raj - will look at this later in detail, but I think you have suggested something similar to what I have tried already except the dynamic input, so will look at that.

Hi @binuacs - Sorry if I wasn't clear. There is one data source initially that splits off into 4 flows - A, B, C and D. All four workflows have different outputs and calculations required from the one initial input source - it was easier to split them off and do all the calculations in their own flows rather than do it all in one big workflow.

The process is that when A completes - it picks up a template file, saves the template into an output folder then copies the output in the required tab of the copied template, then finally renames the template to include the date  - YYYY.MM_Review.xlsx

The other workflows B, C and D are run to each do their own analysis, and then need to copy their output to their own tabs in the output file from A YYYY.MM_Review.xlsx that was just created.

I have use the block until done tool to ensure that the A completes before B, C and D are started. The issue I am having is getting their workflows to pick up the new file to output to. The output file will be dynamic each month, so need to set the workflow to deal with this.

Thanks.

[Baz123]

OK, I think I have a better way to do what I am trying to accomplish.

I have now scrapped the output method and using a template.

What I have setup is so that all four outputs write to separate files and named tabs so in the end there are four files like: 

_Review (A).xlsx|||A

_Review (B).xlsx|||B

_Review (C).xlsx|||C

_Review (D).xlsx|||D

What can I do to merge all these separate files into one file with each on their own sheet? Each has its own schema, so a straight join wont work...?

Thanks.

[binuacs]

@Baz123 Since the schema is different you can use the batch macro to write all the files into one file with different tabs

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Knowledge-Base/The-Ultimate-Input-Data-Flo...