Block Until Done- Order Prioritization

[ScubaGeek]

I would to suggest to add a configuration in the Block Until Done tool, which allow the user to prioritize the release of a data stream through multiple Block Until Done tools in the same module. 

In the example below, the objective is to update multiple sheets in a single Excel workbook. Each sheet is a different data stream, that cannot be unioned together, therefore making the filtering of a single stream feeding into multiple Block Until Done from that filter solution impossible.

What I would like to be able to do is have a configuration, where Block Until Done #2 will not allow the data stream to pass through until Block Until Done #1 is complete, Then Block Until Done #3 will not pass through the data stream until Block Until Done #2 is complete, and so forth through the all the Block Until Done instances. 

[cgoodman3]

@ScubaGeek 

This is a great orchestration problem, due to conflicts of when writing to multiple Excel tabs. It would be great to see UI options to help users make it easier to work with but I think there are other ways that you can achieve what you are looking for.

- I note you say you can’t union the streams as the schema is different. Prior to the union if you create a formula tool and in that create a new field called full path, and put c:\filepath\workbook.xlsx|||’Sheetname$’ Then in the output tool configuration select the option to update the filename using a field (full path). So while everything gets brought together with the union, the outputted data will be split up as required. You may end up with redundant fields with this approach, so the workaround would be a batch macro to output where you filter for the output you need, and remove null columns prior to output.

Crew macros

- there is a macro called parallel block until done. This allows for two inputs and two linked outputs. Chaining these up will achieve the order of execution you are looking for above.

- prior to each output you can add a wait a second tool. Set these up with a cascade, for example 5 sec, 10 sec, 15 sec… however this will need a bit of trial and error to get the right timings.

[ScubaGeek]

@cgoodman3 thank you for your ideas. It is serendipitous to see your post this morning because as I was driving into work I was thinking about creating an output path field within each stream, union them, and have a single output tool that would Change Entire File Path based on that output path field. I am already doing this for the BUD #2 stream (see below) with the individual store data for multiple concepts.

"C:\Alteryx Output\Alteryx TTM Report.xlsx|||"+[concept]

My concern is exactly what you pointed out, each data stream has unique data fields from the other streams, so now each sheet will have a large number of null fields unrelated to the purpose of that sheet. It has been a day and a half since I messed with building macros, but this looks like it could be a good use case for a refresher.

Otherwise, it looks as if I may need to rebuild how the excel workbook functions, have everything in Alteryx come out as a single sheet and then rebuild the other sheets as look-ups to the Alteryx output sheet. 

It is worth noting, I have been running this module monthly since 2015 and while it would occasionally error, if I just re-ran the module a couple of times it would eventually catch and update all sheets with no error. Since last month and this month I updated to Designer 2022.1 and now it does not run without errors no matter how many times I run it. I tried enabling and disabling the AMP Engine thinking that may be the cause, but neither worked.

I thank you for your help, and will now get busy re-learning macro builds!

Ray

[Rags1982]

I have a few workflows like this. 

One solution I came up with was to add a count records to the '2' output of Block until done 1, then append this to the incoming data stream of Block until done 2, then deselect the count in the data. This way, it will ensure that Block until done 2 does actually wait until the first one is done as it needs to wait for the count records before processing.