Merge two workflows into one excel document

[EC55]

Hi Community,

Hope that you are staying safe and practising social distancing as much as possible!

I am working on a huge project that is an organisation wide data analysis which takes 3 separate data sets, joins them, runs them through a significant number of calculations and then gives me 12 output files at the end. Each output file is identical but filtered to only show data from one department, i.e. there is a HR output file, a Sales output file and so on. 

The problem I have is that I need to combine these two distinct workflows (each with their own headings and data, and very little similarity based on the calculations I have performed) into one output (excel document), with each workflow appearing on its own sheet in excel, for each of the departments in the organisation I work for (that are set up already using filters in the output control box).

See attached screenshots for reference (blacked out areas contain sensitive data). I was using that floating window in the merge snip so i didn't run the full output list for the whole thing, but they are otherwise identical. Ignore the red exclamation marks! I understand how to execute one output but the merging of the two workflows together has me stumped. 

Any help you can provide would be greatly appreciated. 

Cheers

EC

[fmvizcaino]

Hi @EC55 ,

Hope you are staying safe as well 🙂

Attached is a solution using block until done and append tool as lockers to guarantee serialized writing to the same excel file.

Best,

Fernando Vizcaino

[EC55]

Hi Fernando

Thanks for the quick response. I kind of see what you have done, but I am not sure how to replicate that 12 times for each division of the organisation? 

Thanks

EC

[EC55]

Hi Fernando - and others

Not sure if this is even possible? If not can anyone suggest a workaround besides sitting there manually creating it? 

Thanks

EC

[AbhilashR]

Hi @EC55, would something like the attached workflow approach help in your case? This solution assumes you need one final excel output file with multiple tabs broken by department.

[EC55]

Hi @AbhilashR 

Apologies for the delay, my COVID-19 workload went through the roof the last few weeks! I am just catching up now. 

For this workflow, I need one excel document per area. The reason being is that the output file will be sent directly to each area for follow up on the data. We are unable to include other areas on the document for security reasons. I have already set up all the output files in the workflow, where I am struggling is connecting the output files to the workflow. 

The problem I have is that throughout my calculation steps, I have ended up with two distinct workflows, each with their own attributes and data. I need each of these workflows to appear as their own tab on each excel output 

i.e. HR excel output document will contain a tab from workflow #1 and a tab from workflow #2 (both in the same worksheet in Alteryx). This will then be replicated for all 12 areas of the organisation; Marketing, Recruitment etc etc etc

I have attached a snip of the tail end of my worksheet. I have circled each workflow (1 and 2). The outputs I have set up are in the output control box on the right. I can attach the first workflow no problem (currently wireless) it is adding in the second workflow as its own tab in the single output file that I am stuck on. 

Hopefully that makes sense now! 

EC

[AbhilashR]

Hi @EC55, the approach in the attached solution is what I think you are looking for. Running the workflow in this example will create three separate files (Marketing, Sales & HR), with each of them having two separate tabs (Calc1 & Calc2).   

The key to this solution/approach lies in the config I use in Layout and Render tools.

I am hoping the workflow (attached) and the screenshots in this post should be self-explanatory, but please let me know if you would like me to walk you through some of the specifics. Hope this helps!   

[EC55]

Hi @AbhilashR 

Thanks for the quick reply. It looks to me like your workflow is backwards to mine. That is I have two workflows that contain the data for the entire organisation which I then want to split/filter into smaller groups (the outputs). I don't have a workflow for each area of the organisation. Your example looks like you have workflow lines for each area of the organisation which are then being brought together which is the opposite - make sense? 

Thanks for working on it with me though. I am still a relative newbie to Alteryx!
EC

[AbhilashR]

Hi @EC55, I don't quite have a grasp around what you are looking for, but re-reading through our exchanges I am assuming you have two distinct workflows (say wf1.yxmd and wf2.yxmd), each with its own logic. Both workflows will write out to same set of files (broken out by Dept.), and will write to their own tabs in these individual department file If the above is correct, attached are 4 workflows for you to glance over. 

Approach 1

union's all dept. data in each workflow and writes out to multiple files. Not the biggest fan of this approach because it introduces some null rows, but sharing it here just to give the breath of what can be achieved.

Approach 2

Is similar to what you have in your screenshot, just that both workflows will write to same dept. file but into their own tabs.

These approaches work as long as the output tools have Overwrite Sheet (Drop) selected in the Output Options.

Thank you for being patient with us here. Hope this helps!

[logiemeister · Accepted solution]

Hi EC,

My understanding is that you need the following output:

1. 12 excel spreadsheets (based on your data categories - HR etc)
2. each spreadsheet must contain two tabs (the output of the two distinct workflows) 
3. Data overwrite for future versions isn't a challenge

If that is correct, then a simple solution is to filter the categories and then save as separate tabs in a spreadsheet like this 

Here's how it works (simple I know)

Hope this helps.