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

Alteryx Loop

mark_paul
8 - Asteroid

Hi,

 

How to achieve below loop in Alreyx?

 

From:

IDTERMDTINT_RT
1001011/1/20193.5
101112/1/20194
102011/1/20192.25
103910/1/20191.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:

IDTERMMTHDT_NEWDAYSINT_RTFACTORINT_PAY
10010011/1/2019303.510
10010112/1/2019313.5(Previous_Factor-INT_PAY)(INT_RT *DAYS)/360 * Previous_Factor
1001021/1/2020313.5(Previous_Factor-INT_PAY)(INT_RT *DAYS)/360 * Previous_Factor
1001032/1/2020293.5(Previous_Factor-INT_PAY)(INT_RT *DAYS)/360 * Previous_Factor
1001043/1/2010313.5(Previous_Factor-INT_PAY)(INT_RT *DAYS)/360 * Previous_Factor
1001054/1/2020303.5(Previous_Factor-INT_PAY)(INT_RT *DAYS)/360 * Previous_Factor
1001065/1/2020313.5(Previous_Factor-INT_PAY)(INT_RT *DAYS)/360 * Previous_Factor
1001076/1/2020303.5(Previous_Factor-INT_PAY)(INT_RT *DAYS)/360 * Previous_Factor
1001087/1/2020313.5(Previous_Factor-INT_PAY)(INT_RT *DAYS)/360 * Previous_Factor
1001098/1/2020313.5(Previous_Factor-INT_PAY)(INT_RT *DAYS)/360 * Previous_Factor
1011012/1/201931410
1020011/1/2019302.2510
1039010/1/2019311.7510
1039111/1/2019301.75(Previous_Factor-INT_PAY)(INT_RT *DAYS)/360 * Previous_Factor
1039212/1/2019311.75(Previous_Factor-INT_PAY)(INT_RT *DAYS)/360 * Previous_Factor
103931/1/2020311.75(Previous_Factor-INT_PAY)(INT_RT *DAYS)/360 * Previous_Factor
103942/1/2020291.75(Previous_Factor-INT_PAY)(INT_RT *DAYS)/360 * Previous_Factor
103953/1/2010311.75(Previous_Factor-INT_PAY)(INT_RT *DAYS)/360 * Previous_Factor
103964/1/2020301.75(Previous_Factor-INT_PAY)(INT_RT *DAYS)/360 * Previous_Factor
103975/1/2020311.75(Previous_Factor-INT_PAY)(INT_RT *DAYS)/360 * Previous_Factor
103986/1/2020301.75(Previous_Factor-INT_PAY)(INT_RT *DAYS)/360 * Previous_Factor

 

 

 

Final Record:

IDTERMMTHDT_NEWDAYSINT_RTFACTORINT_PAY
1001098/1/2020313.5(Previous_Factor-INT_PAY)(INT_RT *DAYS)/360 * Previous_Factor
1011012/1/201931410
1020011/1/2019302.2510
103986/1/2020301.75(Previous_Factor-INT_PAY)(INT_RT *DAYS)/360 * Previous_Factor

 

Thanks for your help

 

Thanks,

Mark

16 REPLIES 16
RolandSchubert
16 - Nebula
16 - Nebula

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_paul
8 - Asteroid

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_paul
8 - Asteroid

Is there any way to achieve this one using iterative macro?

 

Thanks,

Mark

RolandSchubert
16 - Nebula
16 - Nebula

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.

 

RolandSchubert
16 - Nebula
16 - Nebula

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 ...

13-01-_2020_15-51-32.png

 

TonyA
Alteryx Alumni (Retired)

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_paul
8 - Asteroid

Hi @RolandSchubert /@TonyA , 

 

Thanks for your response. Could you guide me how to proceed with Iterative macro for above problem?

 

Thanks,

Mark

TonyA
Alteryx Alumni (Retired)

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?

RolandSchubert
16 - Nebula
16 - Nebula

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?

Labels