Basic Formula help with my Workflow

[gwmcguire6]

Hey I'm working on workflow to replace a manual check and looking for some help on the formula tool here. I am looking to produce a formula where it picks up the value for Row 1 Matching Trade Names and the SwapPNL is then checked to the Row lower in the data set where the value in Matching Trade Names is the value in Trade Name and if SwapPNL value is same in both rows then OK. Is this possible or would I need to do this in more than one step?

Thanks in advance!

[Qiu · Accepted solution]

@gwmcguire6 
It looks that we can do that with a join tool with key "Matching Trade Names" and "SwapPNL".
We can take a closer look if you can provide a bigger set data for both Input and desired output.

[Warcry]

You'll want to use the multi-formula tool. This tool can look up or down on different columns and rows. 

[Warcry]

Formula something like:  if [Row 1] = [Matching Trade Names] and [Matching Trade Names] = [Row+1:Matching Trade Names] and [SwapPNL] = [Row+1:SwapPNL] then ok else .... 

Hope this makes sense.

If this solves your use case, please mark as resolved.

[gwmcguire6]

getting an error with this IF statement in the multi row formula tool 

[Warcry]

if [Swap|PNL] == [Row+1:Swap|PNL]
and [Matching Trade Names] == [Row+1:Matching Trade Names]
then 1
else 0
endif

1 = True

0 = False

[gwmcguire6]

Thanks so much now everything is coming back as false and going through the data I'd believe we should see all as true from this expression. I believe the issue is because I need to use the absolute value of the SWAP|PNL values. Can you show me how I would add that in the formula above?

[Warcry]

if [Swap|PNL] == [Row-1:Swap|PNL]
and [Matching Trade Names] == [Row-1:Matching Trade Names]
then 1
else 0
endif

[gwmcguire6]

thanks for help I actually went with a second Join tool to then compare Swap|PNL of F1 and Matching Trade Name. Thanks everyone