Dec 9, 2017

Conditional workflows in EasyMorph

A conditional workflow is a sequence of actions that changes depending on a condition. For instance: IF loaded data has no missing dates THEN export it into database ELSE send an email to Pete. If we depict this workflow graphically, it would look as below:

When no dates missing, sending an email doesn't happen.
Workflow when no dates missed

When one or more dates are missing, exporting to database doesn't happen.
Workflow when at least one date is missing.


Such conditional workflow (branching) is arranged in EasyMorph using conditionally derived tables. A regular derived table is a table which replicates the resulting state (data) of its source table, and performs certain transformations with it as well as other actions. If you're not familiar with derived tables check out this tutorial article.

A conditionally derived table does exactly the same, except it only does it when a certain condition is satisfied. If not, then no data is replicated from its source table, and no actions/transformations performed -- all actions and transformations in such table are skipped.

The example mentioned above would look slightly differently, if we use branching similar to conditionally derived tables in EasyMorph:

Branching using conditionally derivation in EasyMorph.

Here in this example we have 2 conditionally derived tables. The conditions in the tables are inverse to each other: in one table the condition is "missing a date?" while in the other it's "NOT missing a date?". Therefore, depending on whether the loaded data is incomplete or not, either one table is entirely skipped or the other.

In an EasyMorph project the example described above would look as follows:
EasyMorph project with a conditional workflow.

The two rightmost derived table are derived conditionally.  Notice that one of the tables is skipped -- you can see it has different title bar color, and a derivation icon with red "X". In this table transformations were not executed, and it has no data.

This project detects if some dates are missing or not by calculating the difference between adjacent  dates. If no dates are skipped then the difference with previous date will always be equal to 1 for all dates. If one or more dates are skipped somewhere, then for the following date the distance will be 2 or more. We calculate the max distance, and merge it into the main table as a new column using the "Peek" transformation. Later, prior to exporting, the column is removed.

In the conditionally derived tables the conditions are as follows:

[Distance, days] = 1, for the upper derived table "Export to DB".

[Distance, days] <> 1, for the lower derived table "Send an email".

You can see that conditions are inverse to each other.Therefore, either one or the other derived table is skipped, but never the both are skipped or executed simultaneously.


The "Derive table" transformation has a switch that tells it whether it should be unconditional or conditional. In the latter case,  there are three possible conditions for derivation:
  • If expression evaluates to TRUE for each row in table
  • If the source table is empty
  • If the source table is NOT empty
In the screenshot below you can see properties of the "Derive table" transformation in the conditional mode:


If after the branching execution should continue regardless of which of the derived tables was executed, then we need to join the two branched flows back into one flow. This can be done using the "Either table" transformation. The transformation compares two tables and picks one that is not empty. Since a skipped conditionally derived table is always empty (i.e. has no data), the "Either table" transformation will always replicate the resulting dataset of the table where the condition was satisfied and transformation were executed.

Conditional workflow with continuation.

In some cases, there is only one branch before continuation. In this case the continuation is derived right from the source table (see below). Make sure that "Either table" is set up to give preference to the conditionally derived table if the source table is never empty.

Workflow with one conditionally derived table and continuation.

The examples about show using conditionally derived tables for IF...THEN...ELSE type of workflows. However, in the same fashion it is possible to arrange SWITCH...CASE type of branching that produces 3 or more branches.