Generating new records based on different scenarios

[jessetaylor_18]

Hi Alteryx Community,

I have the following situation that I need assistance in solving.

Person, Credit/Debit, Amount, Start, End

A, Credit, 1000, 1/1/21, 1/5/21

A, Debit, 500, 1/3/21, 1/6/21

B, Credit, 1000, 1/1/21, 1/20/21

B, Credit, 2000, 1/4/21, 1/20/21

B, Debit, 1500, 1/10/21, 1/20/21

I need to create a workflow that can run through these different situations:

First, take the debit and compare it with the first credit.

1. If the debit amount is greater than the credit, we want to do 2 things:

1. We want to change the end date of the credit to the start date of the debit
2. Take the remaining debit balance and apply it to the next credit in the Person's history (repeat for as many needed)
1. If the debit amount is less than the credit, we want to do 2 things;

1. We want to change the end date of the credit to the start date of the debit
2. Create a new row with the amount being credit – debit balance, the start date of debit and the end date of the credit

Thanks for your help in advance  

[DavidSta]

Hi @jessetaylor_18,

I am not sure if I got the edge cases of your dataset, as the sample you provided is very limited and you didn't share how the expected result for your sample data set should look like.

Generally it feels like it might be a little bit complicated to create it with "Multi-Row-Formula" Tools only.

An iterative macro might be a better idea. If you don't know how to get started with Iterative Macros you can check out this Knowledge Base Post.

Best regards,

David