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
Ref | PastDate | Value | User | Product | Ref1 | PastDate1 | Value1 | User1 | Product1 |
100005 | 20220915 | -10 | F | Apple | 100001 | 20220910 | 50 | A | Apple |
222003 | 20220918 | -20 | G | Apple | 10002 | 20220913 | 20 | B | Apple |
22233 | 20221001 | -60 | S | Apple | 100002 | 20220915 | 30 | C | Apple |
Output: (1Pair) - option one
Ref | PastDate | Value | User | Product |
100001 | 20220910 | 50 | A | Apple |
100005 | 20220915 | -10 | F | Apple |
222003 | 20220918 | -20 | G | Apple |
22233 | 20221001 | -30 | S | Apple |
22233 | 20221001 | -30 | S | Apple |
Output (Muti-pairs) - option two
Ref | PastDate | Value | User | Product |
100001 | 20220910 | 50 | A | Apple |
100005 | 20220915 | -10 | F | Apple |
222003 | 20220918 | -20 | G | Apple |
22233 | 20221001 | -30 | S | Apple |
10002 | 20220913 | 20 | B | Apple |
22233 | 20221001 | -20 | S | Apple |
100002 | 20220915 | 30 | C | Apple |
22233 | 20221001 | -10 | S | Apple |
100002 | 20220915 | 20 | C | Apple |
Thx Again
Solved! Go to Solution.
@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.
@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
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.
@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.
@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:
Ref | Cdate | PastDate | Value | Ref1 | Cdate1 | PastDate1 | Value1 |
100001 | 20221101 | 20220910 | 50 | 100005 | 20221101 | 20220915 | -10 |
10002 | 20221101 | 20220913 | 20 | 222003 | 20221101 | 20220918 | -20 |
100002 | 20221101 | 20220915 | 30 | 22233 | 20221101 | 20221001 | -60 |
200010 | 20221101 | 20220915 | 40 | 330000 | 20221101 | 20221002 | -300 |
300005 | 20221101 | 20220915 | 50 | 322201 | 20221101 | 20221003 | -300 |
130001 | 20221101 | 20221003 | 20 | 353330 | 20221101 | 20221003 | -30 |
32222 | 20221101 | 20221006 | 300 | 322222 | 20221101 | 20221005 | -20 |
315532 | 20221101 | 20221007 | 300 |
Calculation Process:
Direction switch has been mark in red
Output
Ref | Cdate | PastDate | Value |
100001 | 20221101 | 20220910 | 50 |
100005 | 20221101 | 20220915 | -10 |
222003 | 20221101 | 20220918 | -20 |
22233 | 20221101 | 20221001 | -20 |
10002 | 20221101 | 20220913 | 20 |
22233 | 20221101 | 20221001 | -20 |
100002 | 20221101 | 20220915 | 30 |
22233 | 20221101 | 20221001 | -20 |
330000 | 20221101 | 20221002 | -10 |
200010 | 20221101 | 20220915 | 40 |
330000 | 20221101 | 20221002 | -40 |
300005 | 20221101 | 20220915 | 50 |
330000 | 20221101 | 20221002 | -50 |
330000 | 20221101 | 20221002 | -200 |
130001 | 20221101 | 20221003 | 20 |
32222 | 20221101 | 20221006 | 180 |
322201 | 20221101 | 20221003 | -300 |
32222 | 20221101 | 20221006 | 120 |
315532 | 20221101 | 20221007 | 180 |
353330 | 20221101 | 20221003 | -30 |
315532 | 20221101 | 20221007 | 30 |
322222 | 20221101 | 20221005 | -20 |
315532 | 20221101 | 20221007 | 20 |
315532 | 20221101 | 20221007 | 70 |
Thank you Again