Decreasing debt amount by deposit

[evaldas]

Hi All,

I'm quit new with alteryx, but currently working with one process and stuck with one task. What I'm trying to achieve is:

I have a list of debtors:

Debtor ID	Aging Buckets	Debt
1	1	100
1	2	15
1	3	200
1	4	200
1	5	50
1	6	0
1	7	0
1	8	0

And a list of deposits:

ID	Deposit
1	300

What I want to achieve is to cover the debt with amount of deposit starting from the oldest debt.

Basically the result should look like this:

Debtor ID	Aging Buckets	Debt
1	1	100
1	2	15
1	3	150
1	4	0
1	5	0
1	6	0
1	7	0
1	8	0

Hope You will be able to help me on this, as I'm stuck on this for half a week already.

[MarqueeCrew]

If you use a running total tool (https://help.alteryx.com/2018.1/index.htm#RunningTotal.htm) you can then join the deposit by id.

Formula will be used after this for debt as:

IF running_total_debt > deposit
THEN 0
ELSE debt
ENDIF

Cheers,
Mark

[evaldas]

Hi Mark,

Thank You for Your answer, unfortunately I still have a problem. Unless I miss something. So when I run Runing total what I get is:

Debtor ID	Aging Buckets	Debt	Running total debt
1	1	100	100
1	2	15	115
1	3	200	315
1	4	200	515
1	5	50	565
1	6	0	565
1	7	0	565
1	8	0	565

After I use Join Multiple to join with deposits data. Not sure if its correct tool to use. What I get is

Debtor ID	Aging Buckets	Debt	Running total debt	Deposit
1	1	100	100	300
1	2	15	115	300
1	3	200	315	300
1	4	200	515	300
1	5	50	565	300
1	6	0	565	300
1	7	0	565	300
1	8	0	565	300

when I use Your sugested formula, what I get is:

Debtor ID	Aging Buckets	Debt	Running total debt	Deposit	Debt2
1	1	100	100	300	100
1	2	15	115	300	15
1	3	200	315	300	0
1	4	200	515	300	0
1	5	50	565	300	0
1	6	0	565	300	0
1	7	0	565	300	0
1	8	0	565	300	0

Which is not correct. Basicaly what I want to achieve is: for example if same customer have a dedt which is splited by different aging buckets like in first table. And lets say he has a deposit of 300. His deposit will be used to cover the debt starting from the oldest one. So in this case what should happen is: debt of 50 which is in bucket 5 will be covered, and i still have 250 from deposit remaining, which means i can still cover the debt of 200 in bucket 4 and part of the debt in bucket 3. So the result I would like to achieve should look like this:

Debtor ID	Aging Buckets	Debt	Running total debt	Deposit	Debt2
1	1	100	100	300	100
1	2	15	115	300	15
1	3	200	315	300	150
1	4	200	515	300	0
1	5	50	565	300	0
1	6	0	565	300	0
1	7	0	565	300	0
1	8	0	565	300	0

Maybe You have any other thoughts?

[BenMoss · Accepted solution]

Here's a slight adaption in the method original outlined by @MarqueeCrew

Ben

[evaldas]

Thank You Ben,

This is what i was looking for! Thank You very much!