First In First Out Calculation

[wongkenny240]

Hi all,

I have two tables with the following columns

Table A

Name	Date	Debt
A	2019-12-01	10
A	2020-01-01	20
A	2020-02-01	0
A	2020-03-01	10
B	2020-03-01	10

Table B

Name	Date	Repayment
A	2020-02-01	10
A	2020-03-01	10
A	2020-04-01	5
B	2020-04-01	10

I want to use a First in First Out approach to get the remaining debt balance and corresponding date of each debt. For example, for the above, I will get the below

Result Table

Name	Date	Remaining Debt Balance
A	2020-01-01	5
A	2020-03-01	10

The first debt of A at 2019-12-01 is repaid by repayment at 2020-02-01 (10-10=0), the second debt at 2020-01-01 is partially paid by repayment at 2020-03-01 and 2020-04-01 (20-10-5=5), the third debt at 2020-03-01 is never repaid yet. The first debt of B at 2020-03-01 is repay at 2020-04-01 (10-10=0)

How to perform calculation similar to above in Alteryx?

Thanks a lot.

Thanks,

Kenny

[atcodedog05]

Hi @wongkenny240 

This is a stock allocation problem.

Here is a workflow for the task.

Output:

Workflow:

Hope this helps 🙂 Feel to ask if you have any questions

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

[wongkenny240]

The resultant table of the second row should be A instead of B because the debt of B is 0 already.

Result Table

Name	Date	Remaining Debt Balance
A	2020-01-01	5
A	2020-03-01	10

[atcodedog05 · Accepted solution]

Hi @wongkenny240 

Here is a modified workflow for the task.

Output:

Workflow:

Hope this helps 🙂 Feel to ask if you have any questions

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

[atcodedog05]

Happy to help 🙂 @wongkenny240 

Cheers and Happy Analyzing 😀

Feel free to reach out if you face any issues 🙂

[wongkenny240]

Would like to ask one more question, is it possible to do this with an iterative macro?

[atcodedog05]

Hi @wongkenny240 

Yes it would be possible but that would be more hectic.