Showing the reminder calculation process and output

[k3pineapple]

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

[MatthewO]

@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]

@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.

[MatthewO · Accepted solution]

@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]

@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