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

BB_BlockParallelOutput_v01.yxzp

Output

Tips and Tricks

Macros

Best Practices

Accepted answers

All comments

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/843260

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/843260

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

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.

dsandmann

Talk about a late reply @AlexSTeryx, I think I win in that regard lol.

Here's a picture of what I'm referring to in a Macro.

I designed this specific one to do two things for me:

Always ensure that there is a row of data to write to a file (if you have block of data that outputs only headers it will wait until the end of the workflow, even after email)
1. This was creating some different problems for me when trying to copy/move/email files especially when reading in a template excel
Standard way to set file to save to different locations

When not using AMP, this macro should work. The block until done tool when used in this way allows each sheet to be written without causing any issues. The technique doesn't have to be used within a macro like this, you could just use it within your workflow and avoid having to think about connections with parallel blocks or potentially breaking a parallel block upstream by disabling a container etc. If I have to do any outputs outside these parameters that's typically what I'll do.

@ChrisTX - this may also solve your example

Excel Macro Example.yxzp

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703