This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
We have a workflow that we want to schedule 3 times a day but we only want it to execute when all of the required data files are present. I've set up a workflow that does everything I want except for keep checking until the condition is met.
Here's what I have so far:
Looks at a file that contains the day, required start time and number of files expected. For that I just set up a simple Excel file.
Based on the current time, it determines which day routine to check (i.e. Mon, Tues, etc.) and which Start Time is relevant. It then takes the Start Time and Number of Files values as its thresholds.
Reads in all of the available files in a directory that meet a specific format (e.g. DS@CommonSMCBIdata@*.xlsx) and checks their time stamps against the Start Time. If they're within an hour of the desired Start Time, then it flags them to include in the run.
It counts all of the files that have been flagged to run. So long as that total is less than the Number of Files threshold, it just ends. Once that total reaches the Number of Files threshold, it kicks off the full workflow.
On that last item, if the total number of available files is less than the Number of Files threshold, what I'd like it to do is not end but rather wait a couple minutes and then check again and loop through that step until the Number of Files is reached. I assume that is possible but I can't figure out which combination of tools would be required to do that.
Thanks for the reply. However, I believe I'm already doing what the Conditional Runner is doing, i.e. kicking off a module only after a condition is met. What I don't know how to do is loop the module that checks for the condition.
I think a cleverly crafted iterative macro would do the trick. I've attached a proof of concept. If you have any trouble implementing this in your workflow, do let me know. Here is my quick summary of how it works. In the main workflow, you would bring in a dummy input variable =1. For the "KeepRunning" iterative macro, you have 3 inputs : time between checks, directory to check, and files within that directory. For you, you could probably select something like 60 seconds, and then just use the directory and files setup for your directory tool. Within the keep running iterative macro, i've recreated a dummy version of your workflow. You would obviously need to change the text input and put in your existing workflow. The key is that after you check if you've found enough files in the formula tool, I've included "wait a second" macro (credit to http://inviso.dk/2016/waitasecond-alteryx-waiting-tool). That macro does use the run command. After that macro, I append with the input, and then send it through the output. By setting up the iterative macro in the interface designer, the 1 row of data keeps flowing through the macro until the formula is true (it found enough files). I've defaulted it to 100 iterations, but you can change that in the interface designer for the iterative macro. Hope that helps!
Thanks for sending this over...I wasn't aware of the Wait a Second macro. :-) However, after unpacking your modules and stepping through them, I'm still unclear how I would get it to do what I want. It is very possible that I am just having a brain fart and not seeing the obvious, but here's where I'm getting stuck:
I can see that I would want to call the Wait a Sec macro when the initial filter check returns a False (i.e. not enough files are found), but what isn't clear is how I use the output of the Wat a Sec macro to trigger another loop.
In your Keep Running macro, I believe the section below corresponds to the section of mine above. In both, the file check happens and the False stream goes to the Wait a Sec macro. In yours, you are passing the values from the main stream and appending to it the Input number (e.g. 1) and then just checking whether that Input is not null. I can't see how this triggers a loop of the formula check. What am I missing? :-)
It's certainly not obvious! By choosing it to be an iterative macro (instead of batch or standard), 1) The interface designer gives you additional options and 2) the macro input/output tools function differently. 1) The interface designer now gives 3 important new options: iteration input, iteration output, and max iterations. Any macro input (output) tool on your canvas can be selected as the iterative input (output). The max keeps it from running forever. In my mind, it works like this: my initial workflow passes one record to my keep running macro input. When one record makes it to the output (assuming my filter tool returned false), the iterative macro simply passes all the data it receives (my one record) back to the macro input and runs the whole macro again with whatever data it receives. This makes it a unique type of macro.When the filter does finally return true, then 0 records would make it to the macro output based on how i designed the macro. When the output macro receives 0 records on a given iteration, then the iterative macro is done and stops iterating. For a more normal use of the iterative macro, there are some great samples under help > samples > macros. For your case, I was able to just keep passing one record back to the start so that it would rerun the keep running macro until the filter tool returned true.
Looks like I might not be clever enough to make the iterative macro work. :-) But I did stumble upon another approach which seems to work quite well. I've simply added a section to the workflow that references a text file to tell it whether another instance of the same workflow is already running. Specifically:
It checks a specified text file
If the text file contains the single word "Running" then the current instance stops
If the text file contains the phrase "Not Running", then the current workflow continues and updates the text file with the word "Running", which then prevents any subsequent instances from running.
Once a full workflow has completed, it updates the text file with the phrase "Not Running" so that subsequent workflows can run.
Thank you for your suggestions. Even though I didn't end up using them exactly, they got me to think beyond my current approach and come upon one that gets the job done.