How to exactly filter data after Join tool

[TeresaZhang7517]

Hello, I have a problem for extract exactly data now.

For example, I use join tool on table A and table B based on type field, and amount difference is within 0.1. I want extract the data 1 on 1 by same field. If there are multiple matched result, need to extract abs(amount difference) data is closet to 0. And each id(id1 and id2) can only be used once.

table A: 

id1	amount1	type
A1	199.57	OTC
A2	199.58	OTC
A3	199.57	OTC

table B:

id2	amount2	type
B1	199.58	OTC
B2	199.57	OTC
B3	199.56	OTC

when I use Join tool by type field, result as below.

id1	id2	amount1	amount2	diff	type
A1	B2	199.57	199.57	0	OTC
A1	B3	199.57	199.56	0.01	OTC
A1	B1	199.57	199.58	0.01	OTC
A2	B1	199.58	199.58	0	OTC
A2	B2	199.58	199.57	0.01	OTC
A2	B3	199.58	199.56	0.02	OTC
A3	B2	199.57	199.57	0	OTC
A3	B3	199.57	199.56	0.01	OTC
A3	B1	199.57	199.58	0.01	OTC

Now, I need filter join result and want to get the final result as below. But I don't know how to implement it except using iterative macro.

id1	id2	amount1	amount2	diff	type
A1	B2	199.57	199.57	0	OTC
A2	B1	199.58	199.58	0	OTC
A3	B3	199.57	199.56	0.01	OTC

How can I output the correct result?

[ArnaldoSandoval]

Hi @TeresaZhang7517 

I did not get your result on the third row, A3-B3 with a diff of 0.01 because A3 and B2 has a difference of zero.

hth

Arnaldo

[TeresaZhang7517]

Hi @ArnaldoSandoval ,

Thanks very much for your fast reply.

Because B2 data is matched with A1 with a diff of 0. B2 and A1 should not be matched by other data. This is my pain point.

[flying008]

Hi, @TeresaZhang7517 

Any idea as yourself ? This is a very nice case.