Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Decreasing debt amount by deposit

evaldas
5 - Atom

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 BucketsDebt
11100
1215
13200
14200
1550
160
170
180

 

And a list of deposits:

IDDeposit
1300

 

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 BucketsDebt
11100
1215
13150
140
150
160
170
180

 

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

4 REPLIES 4
MarqueeCrew
20 - Arcturus
20 - Arcturus
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
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
evaldas
5 - Atom

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 IDAging BucketsDebtRunning total debt
11100100
1215115
13200315
14200515
1550565
160565
170565
180565

 

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

Debtor IDAging BucketsDebtRunning total debtDeposit
11100100300
1215115300
13200315300
14200515300
1550565300
160565300
170565300
180565300

 

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

Debtor IDAging BucketsDebtRunning total debtDepositDebt2
11100100300100
121511530015
132003153000
142005153000
15505653000
1605653000
1705653000
1805653000

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 IDAging BucketsDebtRunning total debtDepositDebt2
11100100300100
121511530015
13200315300150
142005153000
15505653000
1605653000
1705653000
1805653000

Maybe You have any other thoughts?

BenMoss
ACE Emeritus
ACE Emeritus

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

 

Ben

evaldas
5 - Atom

Thank You Ben,

 

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

Labels