Multi Field Or Multi Row Formula - Help

[LewisS180592]

Hi 

I'm new to using both of these tools & can't quite find the answer i need

In the example below, I need a simple formula to find the difference in the *Value* in line 2 from line 1 & then Line 3 from line 1 & into a new column.

Another element to this problem would be to create a formula which is dynamic to the number of rows within the workflow. In one run there may be 5 line items but in the next there could be 20. Any help on this would be much appreciated.

Thank You!

Name	Value	Location	Concatenate Join
A	117.7	Leatherhead	Driving - Kms
B	135	BlueFin	Driving - Kms
C	116.3	Slough	Driving - Kms

[jdminton]

I would actually not use the multirow tool for this. I would use a summarize and append fields (or join if needed) to bring in the first value to use in a regular formula tool. See attached workflow.

 

[ChrisTX]

Your sample data seems to include only one "group", where every calculation uses the first row.  Will your actual data have more "groups", where a calculation may need to use a different row number?  Would the field [Concatenate Join] be the unique "group" indicator?

I'm thinking about a Tile tool to assign a 1,2,3,etc to each group, then break the data stream into two streams, then a Join tool, then Formula tool.

Chris

[LewisS180592]

You are exactly right - This dataset was showing for only one person but could be any amount going forward.

Would this change your outline above at all?

Thanks

Person Reference	Name	Value	Location	Concatenate Join
1	A	117.7	Leatherhead	Driving - Kms
1	B	135	BlueFin	Driving - Kms
1	C	116.3	Slough	Driving - Kms
2	A	112.4	Leatherhead	Driving - Kms
2	B	129.8	BlueFin	Driving - Kms
2	C	111.1	Slough	Driving - Kms

[jdminton · Accepted solution]

@LewisS180592 my workflow would work the same except you would add the Person Reference to the groupby and sort. Since you would have more than one result, you would need to change the append fields to a join though to match each person.

[jdminton]

See updated workflow

[LewisS180592]

Apologies, i did not see your original response (first time posting here!) - But this solution is perfect! Thank you so much for your help