Alteryx loop

[mark_paul]

Hi,

How to achieve MTH column based on the TERM in Alteryx?

from

ID	TERM
100	10
101	1
102	0
103	9

to

ID	TERM	MTH
100	10	0
100	10	1
100	10	2
100	10	3
100	10	4
100	10	5
100	10	6
100	10	7
100	10	8
100	10	9
101	1	0
102	0	0
103	9	0
103	9	1
103	9	2
103	9	3
103	9	4
103	9	5
103	9	6
103	9	7
103	9	8

Thanks,

Mark

[JosephSerpis · Accepted solution]

Hi @mark_paul I mocked up a workflow that produces your output let me know what you think?

[mark_paul]

Hi Joseph,

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