Get Inspire insights from former attendees in our AMA discussion thread on Inspire Buzz. ACEs and other community members are on call all week to answer!

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

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

 

RolandSchubert
16 - Nebula
16 - Nebula

You'll need one Multi-Row Formula tool for each measure to calculate. I've extended the sample workflow and added the calculation for PR_Pay. Do you expect a lot of measures to calculate?

 

Best,

 

Roland

mark_paul
8 - Asteroid

@RolandSchubert 

 

FACTOR=1 for first month, then Previous month Factor - INT_PAY-PR_PAY

 

Thanks,

Mark

TonyA
Alteryx Alumni (Retired)

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

@TonyA , I have PR_PAY as well in the FACTOR calculation, and PR_PAY calculation uses PREV_FACTOR as well

 

Thanks,

Mark

TonyA
Alteryx Alumni (Retired)

I added PR_PAY. You need to tweak the formula, the second Generate Rows, and the Select for each additional calculated column.  I also noticed that I was adding an extra month for each ID. I fixed that.

RolandSchubert
16 - Nebula
16 - Nebula

I added the solution for the Multi-Row Formula approach. Basically I only extented the formula to calculate "FACTOR" by the additional component, seems to work fine (compared results to Excel worksheet).

 

Best,

 

Roland

Labels