How can I link multiple records to calculate running total?

[rohan146]

The problem I am facing is there is a system we have where Amount Booking has a Booked Amount and a base Booked Reference number. There is a Maturity date for Booked Transaction.(Booked)

Periodically on various dates after booking but before Maturity date partial premature Utilization and Cancellation of Master Reference number record is allowed. (Utilize) (Cancel)

These Operations create new reference numbers for new Transactions.

Only linkage to identify the Booked base Transaction is through manual entry with reference of base booked transaction.

The requirement is to have a mechanism to keep a track of original booked transaction outstanding amount considering Cancel and Utilize records on various dates before maturity.

Eg:-

in this file attached, base booked transaction is AB1234 for USD 250,000

On 10-Aug-19; 12-Aug-19; 16-Aug-19 Transactions AB1235; AB1236 and AB1237 have Canceled or Utilized base booked transaction AB1234 for amounts like USD 25,000; USD 25,000 and USD 100,000 leaving an outstanding amount of USD 100,000 after 16-Aug-19 to 31-Aug-19.

I need to have a mechanism to link these transactions based on AB1234 and have a running total for outstanding amount on each date post Cancel and Utilize Transactions.

[Ladarthure · Accepted solution]

hi @rohan146,

if your transactions numbers are always looking the same, you could use a regex to extract the transaction number from the lines, and then you could use a multi row formula to calculate everything.

[Ladarthure]

@rohan146 find attached a sample workflow to illustrate the previous post

[benakesh · Accepted solution]

Hi @rohan146 ,

The attached wf produces  expected  results .  You may have to test  with  multiple  master  ref nums   . 

[rohan146]

Dear @Ladarthure ,

Thanks a lot.

This solution works perfectly fine for the issue at hand.

Compact design and with no dependency on position of records.

Regards,

Rohan

[rohan146]

Dear @benakesh ,

The solution suggested by you is working as well.

Just the beauty of this Alteryx Designer which allows multiple methods to arrive at same solution.

Regards,

Rohan