Help with Multi-Row formula

[bertal34]

Attached is a sample dataset of different scenarios to try and solve my issue.  GL Transactions and my goal is to put together logic in a workflow to recreate the RECONCILED_FLAG with values “NON-RECONCILED” or “RECONCILED”.  Basically for each Use Case, if there is a transaction with positive amount and another transaction with the same negative amount occurring on the same Date, then they are considered RECONCILED.  If anyone can attach a solution I would greatly appreciate it!

[jrlindem · Accepted solution]

Not winning any tool-golf awards for this one, but it does work.

Essentially, used a Multi-Row-Formula Tool to add a Sort ID to each of the record entries, grouped by USE CASE and Date.

Then, took the entries that were over 0 and split them from those that were under 0 (Filter Tool).  All that’s left, then is to re-join the records on USE CASE, Date, and the new Sort ID which gives us three categories:

• Unmatched positive amounts = Unreconciled
• Unmatched negative amounts = Unreconciled
• Matches records = Reconciled

The matched records just need to be re-split between the left and right records and then all that gets Unioned back together.

Worst case, this gives you something to use to move your work forward.  Best case, it gives you some inspiration to try some new things and solve this in your own way!

Either way, hope it helps.  Workflow example attached.

Cheers, -Jay

[bertal34]

@jrlindem 

Jay, thank you so much this is a good solution!  I was able to make a minor tweak to remove the USE CASE field since I only put that in there for display purposes only.  I appreciate your help!!

Jesse

[jrlindem]

@bertal34 -

That is a great news!  I'm glad it worked for you and more importantly that you were able to adapt it to match your needs.  Cheers, and happy Alteryx'ing!  -Jay