In version 3.7 we will be introducing a new, simpler way to arrange “IF … THEN … ELSE” kind of workflows using conditionally derived tables. Here is how it’s going to work:
Hi Dmitry
I need some with with “branching and continuation”.
Example:
TblEmployee with various fields; Key: Batch number and Band
Another Table called TblBand: - Key: Batch and Band
What I what to do is:
If TblBand is empty, keep all records in TblEmployee else if TblBand has records in it, filter TblEmployee with only those records where Band and Bach are equal in TblBand and TblEmployee.
I will then execute more actions on the data set.
Any suggestions will be appreciated.
I read your articles, but am struggling with this.
Thanks
R
Hi @Rykie,
There are two possible solutions to this:
- Straight forward
- Optimal
Straight forward
If TblBand is empty, keep all records in TblEmployee else if TblBand has records in it, filter TblEmployee with only those records where Band and Bach are equal in TblBand and TblEmployee.
How can we identify if TblBand is empty or not? We can use the "Table metadata" action to obtain the total number of rows in this table. If it's 0 then the table is empty. If not then not.
Since we will need to use TblBand for filtering, we move the calculation of total number of rows into a derived table so that it doesn't stand in the way of further calculations with TblBand. Like that:
Now we need to perform a conditional branching for TblEmployee. Which means that TblEmployee somehow should "know" if TblBand is empty or not. For this, we will bring Total rows from TblBand to TblEmployee using the "Peek" action:
Now, when TblEmployee "knows" how many rows in TblBand. Therefore we can make a conditional branching depending on whether TblBand is empty (i.e. Total rows = 0) or not (Total rows > 0).
For this, we create a conditionally derived table that is only calculated when Total rows > 0. In that table we use "Keep matching" to filter on Band and Batch. In order to filter on multiple fields correctly, we will need to create a composite key from Band and Batch using this expression: [Composite Key] = [Batch] & '|' & [Band] in both tables and then filter on Composite Key:
Finally, we use the "Either table" action to merge both branches together. The conditionally derived table "TblEmployee filtered" is calculated only when Total rows > 0, otherwise it's empty. Therefore, the "Either table" action will only replicate it when it's not empty. Otherwise, it will replicate TblEmployee in its unchanged form:
Optimal
In this task a conditional derivation may not be necessary at all. The "Either table" alone would do it (except for a caveat which is described further in the post). Here is why:
Let's look again at the goal:
If TblBand is empty, keep all records in TblEmployee else if TblBand has records in it, filter TblEmployee with only those records where Band and Bach are equal in TblBand and TblEmployee.
We can rewrite it in a different way:
Filter TblEmployee with only those records where Band and Bach are equal in TblBand and TblEmployee. Don't filter when TblBand is empty (because filtering would produce an empty table).
What would happen if we go ahead and use "Keep matching" even if TblBand is empty? There will be no errors, just the result will be empty (intersection with null produces null).
So the logic would basically be:
Filter TblEmployee with only those records where Band and Bach are equal in TblBand and TblEmployee. If the result is empty then disregard it (and just use the original TblEmployee).
And this is exactly what the "Either table does" -- it replicates the final result of another table only when it's not empty, otherwise it does nothing.
Here is the optimized, simpler workflow:
Caveat
Solutions "Straight forward" and "Optimal" are not equal. They behave differently in a case when TblBand is not empty, but no combination of Batch and Band in it corresponds to Batch and Band in any row in TblEmployee. In this case:
- "Straight forward" keeps the original TblEmployee without changes
- "Optimal" produces an empty table
Here is a sample project with both solutions (each on a separate tab). Enable/disable the import action in TblBand to make it empty or not empty.
conditional-empty-table.morph (9.7 KB)
Book1.xlsx (8.8 KB)
Excellent.
Thank you.
R