The “interval merge” works well for a single condition.
2 conditions Interval Merge.xlsx
However, I have a finance-related requirement where I need to work with two conditions. For example, if my account is a revenue account (accounts ranging from 700 to 797) and if my cost center is between 10 and 19, then xxx. If it is still a revenue account ((accounts ranging from 700 to 797) and my cost center ranges from 20 to 29, then yyy.
If it is an expense account (ranging from 600 to 699) and … then …
I attach a sample data set
What is the best way to handle a case like this? Thanks for your help
I suppose, it’s possible to do interval merge twice, and then only filter the rows where merge results match. See the example below:
double-interval-merge.morph (6.8 KB)
Outstanding… and so simple. I wouldn’t have thought of this “double match” trick.
Many thanks, Dmitry
Still an issue in cases like this one:
Alpha is a good answer if you look at A only
Alpha is also a good answer if you look at B only
But it is not a good solution if you have (A and B)
Any idea how to fix this?
Thanks for your help.
Updated Excel sheet:
2 conditions Interval Merge.xlsx (12.4 KB)
The initial algorithm assumed that Values are unique because they are used for matching. If they are not, then a unique row identifier has to be used for matching. It can be the row number.
The updated workflow and source file are attached.
PS. In your expected result the pair 25, 550 matches ALPHA but that’s incorrect, because table B doesn’t have a match for B = 550.
double-interval-merge.morph (7.3 KB)
2 conditions Interval Merge.xlsx (9.3 KB)
You’re totally right.
Many thanks, Dmitry!