Programmatic Detour

[MarqueeCrew]

I've seen this question before and have run into it myself.  I'd like to see a new tool that would allow a developer (of a workflow) to choose a path of logic based upon criteria known only during the execution of a module.

If LEFT INPUT Count of records < 10,000 THEN Path1 (e.g. use a calgary join)

ELSE Path 2 (e.g. use a standard join)

endif

Thanks,

Mark

[fharper]

Make sure you STAR the topic. Stars are vote for change

_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
CONFIDENTIALITY NOTICE: This email message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information and/or Protected Health Information (PHI) subject to protection under the law, including the Health Insurance Portability and Accountability Act of 1996, as amended (HIPAA). If you are not the intended recipient or the person responsible for delivering the email to the intended recipient, be advised that you have received this email in error and that any use, disclosure, distribution, forwarding, printing, or copying of this email is strictly prohibited. If you have received this email in error, please notify the sender immediately and destroy all copies of the original message.

[Jchantnicki]

I would also like to see a set of tools that can accomplish this as well. There are times when I don't want to activate a set of downstream tools because they will throw off an error and cancel out my workflow.

[PCAM]

Adding my vote and a different use case.

We have developed a tool that dynamically selects dates such as 5th Business day or 1st Monday. I have a number of reports that are dependent on a specific event during the month rather than a specific calendar day. This is not possible to schedule in the current scheduler. Hence the tool.

However, to make the tool work effectively as a scheduling tool, we have to package a production workflow as a macro to open/shut containers when the condition is true (i.e. Today = Business Day 5). Then we have to schedule the workflow to run daily for x number of days. The solution in itself works well and prevents "false" errors from counting against our metrics, but it presents packaging challenges. Do we add all 5th business day reports to a single workflow for execution? What happens if one of the macros fails and a report is not produced, since no "true" error message is generated? If one of the reports needs a change (adding/removing a field), how is the developer going to handle the update and testing of a single report within the macro? How do we prevent these production reports from being inadvertently updated by another user who is interested in using the macro?

A conditional detour without the need to introduce macros or create an interactive app would be a tremendous help. Then we can package our individual workflows as we do for any other workflow that does not need these special scheduling conditions. If the condition is met, the report is produced, but if not met, no error is received. We can output to a browse tool. The workflow for false takes less than a second to run and does not hinder the queuing on the server.

Thanks,

Patsy

[fharper]

PCAM,

interesting problem but I think it a scheduling thing more than a detour thing.  I too was disappointed in the Alteryx scheduler functionality and have quite a few more complex schedule considerations than the Alteryx scheduler addresses.  So I built my own.  Works great.

you consideration of business days might be easier to address in an xlsx where you can record if today is a business day or last or n from last etc.  set up a flow to run right after midnight before anything else and have it look at your business calendar, another file or tab in the main xlsx, to evaluate any criteria you code for and record it in a new instance of a scheduling file.  A job runs it then pulls in that scheduling file looks for if it is a business day or if you get more sophisticated looks to see if it should run for a complex set of conditions being true.  If yes run or if no then don’t.

search on poormon’s scheduler to see more on the idea of building your own scheduler.  But a trimmed down version might suit your needs

[PCAM]

Hi Boldie,

We considered your approach early on, and it is a very good alternative. We have used the Excel type scheduler (NETWORKDAYS) for many years with Access and Excel, so that was the first place we thought to look for this type of controller workflow.

The problem with Excel is you have to maintain the input data. If for some reason, you forget to add holidays for the next year, or the network days calculation is off (there is a known Microsoft issue with certain years), you can't adjust on the fly (for example in a scheduled production workflow).

I spent several months developing a tool within Alteryx itself. Some of my formulas are very simple - is today between days 1 and 7 in the calendar and is today Monday? Then today is Monday number 1. Some are very complex - I am calculating six different holidays based on our HR calendar, when they fall, and if the holiday could have a work holiday date different from the actual holiday. This is built out to 1 year future and 5 years retro. Our tool accurately captures the date of each holiday, and the date when we would not work. This is to align with the batch schedules in our core database and is designed so we can schedule Alteryx workflows where the input is dependent on an update of that core system by scheduled batch jobs.

Now we do not have to depend on someone remembering to update the data conditions in a spreadsheet. It's all contained in Alteryx and is fully dynamic.

This tool combined with a conditional detour that could be configured outside of a macro/interactive app (or some sort of improvement on the filter tool) would completely solve our most complex scheduling conditions while maintaining the integrity of our production reports.

[fharper]

PCAM...

What you describe is essentially what I was describing at a high level...excel is simply a simple easy to work with repository of schedule criteria and a place to store calendar info like holidays and business specific dates of interest for scheduling considerations.  People can add items to the scheduler by adding a row with the name and location of the flow and the various schedule criteria you want applied for that item. could have done it in a DB with forms and data entry controls and such but was moving fast and it was intended to be used by my skilled teammates to productionize their flows vs general end users.  I did later add in some on-demand features for end users to request jobs at will, needed that as we did not have the ALteryx Server and its Gallery.

A workflow reads in the calendar and schedule criteria and applies logic to determine today's values, is it a processing day etc.. and records it in a yxdb along with the current iterations evaluation of what jobs to run now, i.e. which jobs that have been scheduled have met their criteria for execution.  every 10 minutes the scheduler process, a batch file runs the scheduler flow which evaluates and sets the run trigger, then runs another flow which reads the yxdb back in and produces batch files to execute the jobs, whether yxmd, yxmc, or exe, etc.  you can run anything that can be run from command line.  the batch jobs that are dynamically built and stored for reference/troubleshooting as well as all output views of alteryx modules are captured into logs files for troubleshooting.  another job runs weekly to delete old logs.

Never needed the detour but I was able to provide sufficient criteria, some static and some dynamic, to determine whether a job should run.  PS some jobs were also broken into components, separate jobs, so facilitate select execution of certain code parts since no programmatic detour exists.  just had a thought though...might be able to influence the existing detour tool by setting a value based on your data or lack of data in a path and then connecting that to a drop down tool and hence thru action tool to change the Detour tool option selected...might play with that later.

Anyway the poorman's scheduler reference is to an old post on a fairly full featured system I built at what is now a former employer.  I never provided code or great detail but provided enough info to help anyone with initiative to build their own version. Sounds like you have done similar though likely somewhat different functionality based on your particular needs.

[fharper]

To reiterate...the xlsx is more a storage medium than anything.  the calendar is a basic population n years forward where common holidays are captured and business specific dates of interest, business quarter, non-process days (where the data center may be planned to be down or something) finance dates etc.  anything date related that is known in advance is recorded and annually updated as needed.  

All the logic regarding nature of a day is based on Alteryx formulas, first/last business day of the month as we consider it based on weekends, holidays and other blackout dates we have in logic and in the calendar source (xlsx)...and so on

[PCAM]

You make a fair point. All of our black-out days and special considerations are programmed into Alteryx. One-off and special scheduling is done either on our test server or desktops. Our management does not do those on our production scheduler. Too many users and too much overhead can jam up the production reporting, some of which is critical management reporting.

I would still like to see a tool - programmable detour, programmable filter, etc. that would work as a start/stop for part of a workflow. If a condition is true, then run this. If it is false, run this other thing. That's actually a pretty common question on the community with lots of great ideas.

Thanks!

[RachelW]

I have added this idea to consider when we make changes to the Detour tool. 

[saqib]

Thanks @RachelW!!! :)