Building Block - Block Parallel Output

[AlexSTeryx]

Hi Alteryx Community, 

as introduced here this is our Building Block to block parallel outputs.

"Enables output in several sheets of one Excel file without causing an error that the file is blocked"

The Download of the macro you might find following this path.

Of course there are certainly tons of more applications for the macro "block until done".

Feel free to use it, replay, like it and share it!

Best

Alex

[ChrisTX]

@AlexSTeryx thanks for posting your workflow.

In the text of your post above, I think this part of the second line refers to another Building Block: "this is our Building Block to calculate a ranking"

When writing to Excel, a common use case is to write different layouts to different Sheets.  How do you handle this?  The only solution I've found is to use the CReW macro Wait a Sec, to allow time for Excel to completely shut down before trying to write to the same file again.  The waiting increases the run time of one of my workflows by 50%.  Sure wish Alteryx would address this common issue.

Here's a related Idea, if you'd care to add a Like: https://community.alteryx.com/t5/Alteryx-Designer-Ideas/Enhance-options-for-Excel-output/idi-p/84326...

Chris

[AlexSTeryx]

Hi @ChrisTX , 
thanks for the hint to the wrong reference! Typical copy & paste bug.

I'm not sure whether I understand you correctly. But the WF provided here does exactly do what you describes: first writing into sheet 1 of an Excel sheet and after this is done writing into sheet 2 of the same Excel sheet.

I admit I did not measure the increase of the runtime for my WFs when using the block until done macro.

Will have a look at your article. 

Best 

Alex

[ChrisTX]

I don't think your workflow can handle different layouts written to different Excel sheets.  You have only one input, so each output Sheet would need to be the same format.

Chris

[AlexSTeryx]

Hi @ChrisTX ,

I used a similar set up in a WF where I wrote different outputs on different sheet within the same Excel file. With in this Excel file the sheets were differently formatted.
What exactly do you mean with " same format" or "different layout" respectively?
Best

Alex

[ChrisTX]

What I mean by " same format" or "different layout".....

The "same format" (or "same layout") means....

   Each data stream has the same field names with the same data types.

With a workflow like the one in this screenshot, if you wanted to save a different data stream to the same Excel output file, you may get a file write contention error.

It looks like your workflow only handles one data stream being written.

If you add a second data stream, writing to the same Excel file, you could run into a file lock issue. (depending on how quickly the first data stream is written to Excel, and Excel completely closes).

Chris

[AlexSTeryx]

Hello @ChrisTX,

now I see what you're hinting at. Thanks.

Bot why do you not - in the picture / WH above  - add a 3rd  "Block Until Done" Macro, where the Data Stream 2 goes through the "2" anchors and and the left "1" anchor is connected to the right  "2" anchor of the 2nd "Block Until Done" Macro. So that this stream goes then from the right "1" anchor of the third "Block Until Done" Macro to the 3rd Output Tool.

Something like this: 

Since the Data Stream 2 is obviously the fasted it might be an idea to place it above the data stream 1 and run it there through an "Block Until Done" Macro. Then the sheet 9 would be filled first,and the others afterwards.

Actually I checked my workflows and did even have one with several data streams all writing on the same sheet of the same Excel File:

Sorry, for the small picture, but could not enlarge further  the WF on my laptop screen. But you can see the different inputs on the left and the four outputs on the right are all "separated" by the "Block Until Done" macro.

Best

Alex 

[ChrisTX]

Yes, I could  do that.  But it gets very messy for only 2 data streams.  In one of my "reports" with output to one Excel file, separate tabs, I have 20 separate data streams.

And that approach is basically what I'm doing, using Wait a Sec instead of Parallel Block Until Done, in the screenshot in my idea here: https://community.alteryx.com/t5/Alteryx-Designer-Ideas/Enhance-options-for-Excel-output/idi-p/84326...

My feedback to Alteryx is.... this is a common use case, writing different data streams (with likely different layouts) to one Excel file.  Yet there's no easy way to do it.

Chris

Chris

[AlexSTeryx]

@ChrisTX 

One dead you have to die;-)

Maybe use wireless connections. And as you can see in my WF above, I try to align Inputs, similar steps in the WFs, and the outputs vertically, to avoid the mess. Doing so you easily can place the macros just before your outputs. 

What do you think?

c u

Alex

[dsandmann]

Another option for you, just put a regular block before done in front out each output tool. I've used this technique inside macros that wrap around outputs to do things like make it easier to set up, force a write at the correct time even if no data passes through, or even to bring in run commands that action files by autofitting columns widths etc.