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 RefPastDateValueUserProductRef1PastDate1Value1User1Product110000520220915-10FApple1000012022091050AApple22200320220918-20GApple100022022091320BApple2223320221001-60SApple1000022022091530CApple Output: (1Pair) - option one RefPastDateValueUserProduct1000012022091050AApple10000520220915-10FApple22200320220918-20GApple2223320221001-30SApple2223320221001-30SApple Output (Muti-pairs) - option two RefPastDateValueUserProduct1000012022091050AApple10000520220915-10FApple22200320220918-20GApple2223320221001-30SApple100022022091320BApple2223320221001-20SApple1000022022091530CApple2223320221001-10SApple1000022022091520CApple Thx Again

Showing the reminder calculation process and output

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

Workflow

Dynamic Processing

Preparation

Accepted answers

MatthewO

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

IterativeMacro.yxzp

Usage.yxzp

All comments

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

HCalc_Test.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703