Match on Opposite direction & Non-matching Amounts within tolerance

[nsarayar]

Hi

I'm trying to match on the following - Currency must be the same, Origin must be opposite, Amounts must be opposite directions but can be within 1% tolerance. Could anyone help?

Currency	Origin	Amount	Match ID
USD	Our	50	1
USD	Their	-50	1
AUD	Our	100	2
AUD	Their	-99	2
BRL	Our	20	3
SAR	Our	-20	4

[adamorse]

I'm assuming you mean you want to create something like the Match ID field you have in the screenshot? But maybe I'm misunderstanding.

If so, you might try something like:

• filter on [origin]="Our"
• join the true stream of the filter tool to the false stream on [Currency]=[Currency] so those match
• filter requiring the absolute value of the left [Amount] field plus the right [Amount] field being less than or equal to 1 (so these are opposites within 1)
• add a recordID to generate the match ID

[cplewis90 · Accepted solution]

Hello @nsarayar 

I would agree with @adamorse on the steps, but change one thing. The last filter I would do your 1% calculation. I have attached a workflow with an input toll of your data. I adjusted the data for one row so you could see the 1% tolerance filtering one record out. Hope this helps!

[nsarayar]

Thank you @cplewis90 !! I tried this before seeing your solution in such a roundabout way and your solution worked a treat in a few steps