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

Rent Amortization Table

MatthewK
5 - Atom

 

Hello everyone,

 

I am looking for help populating out a rent schedule for several leased properties. I have a table with the length of the lease, the total rent, and rent values & durations in months for each rent step, like the following:

Months

Total Rent

Rent Step 1Step 1 DurationRent Step 2Step 2 DurationRent Step 3Step 3 Duration...
484059001100031127531155012 
36

472920

127501213135121352512 
19750975010000 

 

I need these steps broken out month-by-month, so that there is a column for rent each month at each of the properties, like so:

MonthsTotal RentMonth 1Month 2Month 3Month 4Month 5Month 6Month 7...
4840590011000110001100011275112751127511550 
3647292012750127501275012750127501275012750 
197509750000000 

 

Both the length of the rent steps and the total number of steps in the contract vary per property.

 

Any help would be greatly appreciated!

2 REPLIES 2
Luke_C
17 - Castor

Hi @MatthewK 

 

I put together a quick sample on how you could accomplish this.

 

  1. First data is transposed to get the step info as rows rather than columns
  2. Then I use the filter to separate the durations from the rent
  3. I update the fields to be standard 'Step X' rather than step 1 rent, step 1 duration, etc. This comes into play later on.
  4. Then I generate a row for each month of the step duration, and use a multi-row formula tool to get the overall month order correctly.
  5. Then I join that data up with the rent information and cross tab.
  6. The dynamic rename adds 'Month' to the column headers. 

 

Let me know if this works for you or if you have any questions. 

 

 

Luke_C_0-1617730782069.png

 

MatthewK
5 - Atom

Thank you Luke, this worked like a charm! Exactly the functionality I was looking for.

Labels