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