Multi Field Or Multi Row Formula - Help

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

Alteryx Query.png

Preparation

Accepted answers

jdminton

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

All comments

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.

Distance.yxmd

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

See updated workflow

Distance.yxmd

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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703