We’ve extended Inspire Early Bird Pricing until March 1. Register now and enjoy 20% off conference passes and 10% off training passes. P.S. Don’t forget to bring friends! When you sign up for five or more tickets, you get an extra 20% discount on conference passes. Learn more now.

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Showing the reminder calculation process and output

k3pineapple
8 - Asteroid

Hi Team,

 

Need your support to show the output below

 

take the first value on the right(Value1), mins the left value(Value). Doing the calculation until the right side value become 0 and show the right side value's reminder.

 

Thank you very much

 

Input

RefPastDateValueUserProductRef1PastDate1Value1User1Product1
10000520220915-10FApple1000012022091050AApple
22200320220918-20GApple100022022091320BApple
2223320221001-60SApple1000022022091530CApple

 

Output: (1Pair) - option one

RefPastDateValueUserProduct
1000012022091050AApple
10000520220915-10FApple
22200320220918-20GApple
2223320221001-30SApple
2223320221001-30SApple

 

Output (Muti-pairs) - option two 

RefPastDateValueUserProduct
1000012022091050AApple
10000520220915-10FApple
22200320220918-20GApple
2223320221001-30SApple
100022022091320BApple
2223320221001-20SApple
1000022022091530CApple
2223320221001-10SApple
1000022022091520CApple

Thx Again

4 REPLIES 4
MatthewO
Alteryx
Alteryx

@k3pineapple I believe you could approach a solution using the Append Fields tool to create all possible record combinations, and then a Multi-Row Formula tool to conduct the row-by-row calculation. I'm not understanding the logic completely to arrive at your solution above. If you could share a workbook with the calculations you are using, I can try to assist more. 

k3pineapple
8 - Asteroid

@MatthewO Thank you very much for your help here. This is my current workflow, which can generate the reminder now.

 

I need somehow put the reminder into the this calculation

k3pineapple_1-1667808729553.png

 

Rule #1

in this image, 1st row 50-10 get 40, I would like to let 40-20(row 2) first instead of 20-20

Rule #2

After caluculate 40-20, get 20, use this 20 to minus 60, since the result(20-60=-40) is smaller than 0.... -60 will split to -20 & -40

-20 will make the balance as 0.

-40 is going to match the 2nd value:20 in the value column....since 20-40 =< 0 , -40 will split into -20 & -20

1st.-20 is going to match 20 to be 0, 20-20 =0

2nd -20 is going to match 30, 30-20=10

 

Not sure this logic can be applied in the Alteryx, may be use some python scripts? to avoid the confusion I removed the user column no need for that.

 

Thank you again.

MatthewO
Alteryx
Alteryx

@k3pineapple I was able to replicate your sample output by using an Iterative Macro. I have attached the macro as well an example of it's usage in a workflow. You could continue to tune the logic to suit your needs but I hope this is helpful for you.

k3pineapple
8 - Asteroid

@MatthewO Thank you so much for your kind assistance. this macro helps me resolve the reminder problem.

 

I know maybe it is a little too much here...I hope you still may help me on this

Kind wondering whether you may help me to add a sector in order to compare the PastDate columns in this macro?

Such that 

if the Pastdate < Pastdate1 ( ie. 20221001 < 20220930) calculation side reverse, using Right_Value matching the Value columns. the reminder from Right_Value will be the start value to match Value

 

Data Source:

RefCdatePastDateValueRef1Cdate1PastDate1Value1
1000012022110120220910501000052022110120220915-10
100022022110120220913202220032022110120220918-20
100002202211012022091530222332022110120221001-60
2000102022110120220915403300002022110120221002-300
3000052022110120220915503222012022110120221003-300
1300012022110120221003203533302022110120221003-30
3222220221101202210063003222222022110120221005-20
3155322022110120221007300    

 

Calculation Process:

k3pineapple_0-1667974462425.png

 

Direction switch has been mark in red

 

Output

RefCdatePastDateValue
100001202211012022091050
1000052022110120220915-10
2220032022110120220918-20
222332022110120221001-20
10002202211012022091320
222332022110120221001-20
100002202211012022091530
222332022110120221001-20
3300002022110120221002-10
200010202211012022091540
3300002022110120221002-40
300005202211012022091550
3300002022110120221002-50
3300002022110120221002-200
130001202211012022100320
322222022110120221006180
3222012022110120221003-300
322222022110120221006120
3155322022110120221007180
3533302022110120221003-30
315532202211012022100730
3222222022110120221005-20
315532202211012022100720
315532202211012022100770

 

 

Thank you Again

Labels