Hi, I am trying to select or discard rows based on a combination of values. I'm pretty sure I need a Multi-Row Formula but can't work out the logic.
Downstream in my workflow is a sort by Ref No then by Effective From then a Unique tool to pick up the Condition Type with the most recent Effective From date, but I have 2 instances in my example data below where this is not happening correctly - Ref No 137972 and 200672.
My dataset is much larger, I have just included a small subset below.
For each Ref No, there can be up to 3 Condition Types.
I want to create the column called Select or Discard as part of the process
The logic I am looking for is:
If there is an IFRS16 Rent Payables and an IFRS16 Rent Refund Condition Type for the same Ref No AND either the Effective From or Valid to dates are the same, then they cancel each other out and should therefore be discarded, leaving the Rent Payables Condition Type (in the below example this is true for 137972)
If there is a Rent Payables and a Rent Refund Condition Type for the same Ref No AND either the Effective From or Valid to dates are the same, then they cancel each other out and should therefore be discarded, leaving the IFRS16 Rent Payables Condition Type (in the below example this is true for 200672)
How do I build this logic into my workflow please? Thanks in advance for any help.
I'm working this out but might not have time to finish this morning. To help with the Condition Type logic, I would use a Summarize to concatenate the values and then join that back to the main data set so you can use it in logic.
This may be a bit convoluted, but I think it will get the job done. Essentially, I ran both of your logic tests using Cross Tab tools in order to have each record on one row. I then applied the logic test and filtered out records that didn't match. Those matching records are signaled with a Find and Replace and using two Unique tools and a Union (with specific output order) in between did the trick.
Discarding rows based on combination of values - Solution MML.yxmd
Hi @FreeRangeDingo - I am looking forward to seeing more of your proposed solution, as I am not quite sure yet what your next steps are, but I really appreciate you taking the time to work through it for me.