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
Hi @mark_paul ,
interesting problem. The rows for each month can easily be generated using the Generate Rows tool (using DateTimeAdd function), but calculation of INT_PAY and FACTOR both depend on the calculation of the previous row of the respectively other measure, but I think you can solve it using a combination of Multi-Row Formula tools and a Formula tool. I've attached a sample workflow.
What do you think?
Best,
Roland
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks Roland for your response.
I see that FACTOR calculation you always used 1-INT_PAY, but actual calculation is Previos_Month_Factor Minis Current Month INT_PAY
Thanks,
Mark
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Is there any way to achieve this one using iterative macro?
Thanks,
Mark
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
My calculation is 1- INT_PAY_accum. The first Multi-Row Formula calculate the accumulated INT_PAY. The next step calculates FACTOR based on these values, finally using a second Multi-Row Formula tool to calculate INT_PAY.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Of course, there are almost always different approaches. The "Multi-Row-Formula" approach seems a bit easier. And as a quick check in Excel returned the same results ...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I tried a slightly different approach from @RolandSchubert to try to use your exact formulas. It's a little weird, but what I did was build the calculations in one column alternating INT_PAY and FACTOR values, so I could always refer to the previous two rows for any calculation. It basically looks like your table with INT_PAY and FACTOR transposed. Then I crosstabbed after doing the calcs.
I'm pretty sure this could be done with an iterative macro, but I suspect that would run more slowly than @RolandSchubert 's or my solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @RolandSchubert /@TonyA ,
Thanks for your response. Could you guide me how to proceed with Iterative macro for above problem?
Thanks,
Mark
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
There's certainly room for a lot of different opinions here but I don't think this is an ideal problem for an iterative macro. You need to process the rows in overlapping groups of two. If you wanted to do this with an iterative macro, I think you would need to add a RecordID column and use the IterationNumber with RecordID to identify the two rows to process in each pass. You can peel off one row each cycle, but for every cycle you would have to look at every remaining row to determine if it needs to be processed in that pass.
The solutions presented so far do the calculations with a single pass through the data. The iterative macro I'm describing would require at least n(n-1)/2 passes on a data set with n rows. This would involve a lot of extra computation, make for a more complex, slower workflow, and still give the same result. Others might be able to come up with a more effective scheme, but I can't see how an iterative macro could be more efficient than a single pass solution in this case.
If you really want to pursue this, I would suggest starting a new message thread. You'll need to get more eyes on this and this thread is already marked as solved so it won't get much attention. @RolandSchubert , do you have anything to add?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I absolutely agree with @TonyA . I would not expect a macro solution to be faster, but it would add complexity. In general, I want solutions to be as simply as possible. Of course, it maybe interesting as an exercise - finding a different solution is never a bad idea from a knowledge acquisition point of view ...
Do you see a specific reason to use an iterative macro?