Hi, How to achieve below loop in Alreyx? From: IDTERMDTINT_RT1001011/1/20193.5101112/1/20194102011/1/20192.25103910/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_PAY10010011/1/2019303.51010010112/1/2019313.5(Previous_Factor-INT_PAY)(INT_RT *DAYS)/360 * Previous_Factor1001021/1/2020313.5(Previous_Factor-INT_PAY)(INT_RT *DAYS)/360 * Previous_Factor1001032/1/2020293.5(Previous_Factor-INT_PAY)(INT_RT *DAYS)/360 * Previous_Factor1001043/1/2010313.5(Previous_Factor-INT_PAY)(INT_RT *DAYS)/360 * Previous_Factor1001054/1/2020303.5(Previous_Factor-INT_PAY)(INT_RT *DAYS)/360 * Previous_Factor1001065/1/2020313.5(Previous_Factor-INT_PAY)(INT_RT *DAYS)/360 * Previous_Factor1001076/1/2020303.5(Previous_Factor-INT_PAY)(INT_RT *DAYS)/360 * Previous_Factor1001087/1/2020313.5(Previous_Factor-INT_PAY)(INT_RT *DAYS)/360 * Previous_Factor1001098/1/2020313.5(Previous_Factor-INT_PAY)(INT_RT *DAYS)/360 * Previous_Factor1011012/1/2019314101020011/1/2019302.25101039010/1/2019311.75101039111/1/2019301.75(Previous_Factor-INT_PAY)(INT_RT *DAYS)/360 * Previous_Factor1039212/1/2019311.75(Previous_Factor-INT_PAY)(INT_RT *DAYS)/360 * Previous_Factor103931/1/2020311.75(Previous_Factor-INT_PAY)(INT_RT *DAYS)/360 * Previous_Factor103942/1/2020291.75(Previous_Factor-INT_PAY)(INT_RT *DAYS)/360 * Previous_Factor103953/1/2010311.75(Previous_Factor-INT_PAY)(INT_RT *DAYS)/360 * Previous_Factor103964/1/2020301.75(Previous_Factor-INT_PAY)(INT_RT *DAYS)/360 * Previous_Factor103975/1/2020311.75(Previous_Factor-INT_PAY)(INT_RT *DAYS)/360 * Previous_Factor103986/1/2020301.75(Previous_Factor-INT_PAY)(INT_RT *DAYS)/360 * Previous_Factor Final Record: IDTERMMTHDT_NEWDAYSINT_RTFACTORINT_PAY1001098/1/2020313.5(Previous_Factor-INT_PAY)(INT_RT *DAYS)/360 * Previous_Factor1011012/1/2019314101020011/1/2019302.2510103986/1/2020301.75(Previous_Factor-INT_PAY)(INT_RT *DAYS)/360 * Previous_Factor Thanks for your help Thanks, Mark

Alteryx Loop

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

Developer

Developer Tools

Batch Macro

Dynamic Processing

Accepted answers

RolandSchubert

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

AlteryxLoopSample.yxmd

TonyA

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.