Alteryx Loop
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi,
How to achieve below loop in Alreyx?
From:
ID | TERM | DT | INT_RT |
100 | 10 | 11/1/2019 | 3.5 |
101 | 1 | 12/1/2019 | 4 |
102 | 0 | 11/1/2019 | 2.25 |
103 | 9 | 10/1/2019 | 1.75 |
DT_NEW=DT increment by 1 month
DAYS=Number of days in that month
FACTOR=1 for first month, then Previous month Factor - INT_PAY
INT_PAY=0 for First Month, then (INT_RT *DAYS)/360 * Previous_Factor
To:
ID | TERM | MTH | DT_NEW | DAYS | INT_RT | FACTOR | INT_PAY |
100 | 10 | 0 | 11/1/2019 | 30 | 3.5 | 1 | 0 |
100 | 10 | 1 | 12/1/2019 | 31 | 3.5 | (Previous_Factor-INT_PAY) | (INT_RT *DAYS)/360 * Previous_Factor |
100 | 10 | 2 | 1/1/2020 | 31 | 3.5 | (Previous_Factor-INT_PAY) | (INT_RT *DAYS)/360 * Previous_Factor |
100 | 10 | 3 | 2/1/2020 | 29 | 3.5 | (Previous_Factor-INT_PAY) | (INT_RT *DAYS)/360 * Previous_Factor |
100 | 10 | 4 | 3/1/2010 | 31 | 3.5 | (Previous_Factor-INT_PAY) | (INT_RT *DAYS)/360 * Previous_Factor |
100 | 10 | 5 | 4/1/2020 | 30 | 3.5 | (Previous_Factor-INT_PAY) | (INT_RT *DAYS)/360 * Previous_Factor |
100 | 10 | 6 | 5/1/2020 | 31 | 3.5 | (Previous_Factor-INT_PAY) | (INT_RT *DAYS)/360 * Previous_Factor |
100 | 10 | 7 | 6/1/2020 | 30 | 3.5 | (Previous_Factor-INT_PAY) | (INT_RT *DAYS)/360 * Previous_Factor |
100 | 10 | 8 | 7/1/2020 | 31 | 3.5 | (Previous_Factor-INT_PAY) | (INT_RT *DAYS)/360 * Previous_Factor |
100 | 10 | 9 | 8/1/2020 | 31 | 3.5 | (Previous_Factor-INT_PAY) | (INT_RT *DAYS)/360 * Previous_Factor |
101 | 1 | 0 | 12/1/2019 | 31 | 4 | 1 | 0 |
102 | 0 | 0 | 11/1/2019 | 30 | 2.25 | 1 | 0 |
103 | 9 | 0 | 10/1/2019 | 31 | 1.75 | 1 | 0 |
103 | 9 | 1 | 11/1/2019 | 30 | 1.75 | (Previous_Factor-INT_PAY) | (INT_RT *DAYS)/360 * Previous_Factor |
103 | 9 | 2 | 12/1/2019 | 31 | 1.75 | (Previous_Factor-INT_PAY) | (INT_RT *DAYS)/360 * Previous_Factor |
103 | 9 | 3 | 1/1/2020 | 31 | 1.75 | (Previous_Factor-INT_PAY) | (INT_RT *DAYS)/360 * Previous_Factor |
103 | 9 | 4 | 2/1/2020 | 29 | 1.75 | (Previous_Factor-INT_PAY) | (INT_RT *DAYS)/360 * Previous_Factor |
103 | 9 | 5 | 3/1/2010 | 31 | 1.75 | (Previous_Factor-INT_PAY) | (INT_RT *DAYS)/360 * Previous_Factor |
103 | 9 | 6 | 4/1/2020 | 30 | 1.75 | (Previous_Factor-INT_PAY) | (INT_RT *DAYS)/360 * Previous_Factor |
103 | 9 | 7 | 5/1/2020 | 31 | 1.75 | (Previous_Factor-INT_PAY) | (INT_RT *DAYS)/360 * Previous_Factor |
103 | 9 | 8 | 6/1/2020 | 30 | 1.75 | (Previous_Factor-INT_PAY) | (INT_RT *DAYS)/360 * Previous_Factor |
Final Record:
ID | TERM | MTH | DT_NEW | DAYS | INT_RT | FACTOR | INT_PAY |
100 | 10 | 9 | 8/1/2020 | 31 | 3.5 | (Previous_Factor-INT_PAY) | (INT_RT *DAYS)/360 * Previous_Factor |
101 | 1 | 0 | 12/1/2019 | 31 | 4 | 1 | 0 |
102 | 0 | 0 | 11/1/2019 | 30 | 2.25 | 1 | 0 |
103 | 9 | 8 | 6/1/2020 | 30 | 1.75 | (Previous_Factor-INT_PAY) | (INT_RT *DAYS)/360 * Previous_Factor |
Thanks for your help
Thanks,
Mark
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you @RolandSchubert and @TonyA for your response.
If previous factor is used in multiple attributes then how to use the Multi-row formula?
DT_NEW=DT increment by 1 month
DAYS=Number of days in that month
FACTOR=1 for first month, then Previous month Factor - INT_PAY-PR_PAY
INT_PAY=0 for First Month, then (INT_RT *DAYS)/360 * Previous_Factor
PR_PAY=0 for First Month, then INT_RT/12 * Previous_Factor
Thanks,
Mark
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
In my workflow, I did this by starting with separate rows for FACTOR and INT_PAY and having all values in one column. This is hard to explain, but easier if you look at the workflow.
I use the GenerateRows tool to create a new column (ColNum) that has two rows for each original row. This column contains a 1 or 0 value to indicate whether the row will contain a FACTOR value or an INT_PAY value. I then create a Value column with a Formula tool that includes an IF statement to select the appropriate calculation based on the ColNum value.
With all the values in one column if the row is an INT_PAY row, then Row-1 has the previous FACTOR, and if the row is a FACTOR row, then Row-1 has the INT_PAY value and Row-2 has the previous FACTOR. So I can easily reference previous row values regardless of whether the value is INT_PAY or FACTOR. We can start at the top and calculate each value as we run down the column, using values from previous rows in that column.
Once the calculation is finished, I use a transpose tool to create separate columns labeled 1 and 0 from ColNum and Value then rename the columns to INT_PAY and FACTOR.
This workflow has the advantage of using the original formulas and doesn't need intermediate results, but it is a little harder to understand. @RolandSchubert 's workflow rewrites the formulas slightly but is easier to follow once you understand the revised formulas.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@TonyA , I have PR_PAY as well in the FACTOR calculation, and PR_PAY calculation uses PREV_FACTOR as well
Thanks,
Mark
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
![](/skins/images/59DA3CA97FC6306BFE8B6DED203F3AC0/responsive_peak/images/icon_anonymous_message.png)
- « Previous
-
- 1
- 2
- Next »