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 IDAging BucketsDebt11100121513200142001550160170180 And a list of deposits: IDDeposit1300 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 IDAging BucketsDebt11100121513150140150160170180 Hope You will be able to help me on this, as I'm stuck on this for half a week already.

Decreasing debt amount by deposit

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.

Preparation

Accepted answers

BenMoss

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

Ben

MultiRow Solution.yxmd

All comments

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?

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703