Join Data with Match in one Column and Non-Match in other Column

[Travis_Ratliff]

Hello All,

I have two sets of data one labeled "New Transactions" and the other labeled "Original Transactions". What I am trying to do is find all the rows in the "New Transactions" where the transaction ID matches BUT the quantity does not match the original transactions.

I originally thought this could be done with just a join but as you can see in the sample workflow attached the left join is producing 8 rows instead of the intended 7 rows. This is because one of the transaction IDs is not in the "Original Transactions". 

Any help in how to solve this would be greatly appreciated.

Thanks

[ShankerV]

Hi @Travis_Ratliff 

Are you referring to the transaction AB_24680?

[ShankerV]

Hi @Travis_Ratliff 

As far I have seen the join tool you have used, it is working perfectly as it need to behave.

Might be you are expecting some other result and used Join tool, assuming that will help.

Can you please share your expected output, will suggest the right tool and steps to get the output.

Many thanks

Shanker V

[Travis_Ratliff]

Sorry for the confusion.

I only want the transactions that are in the "Original Transactions" but have a different quantity.

So, my intended result is the 8 transactions in the left join not including AB_24680, so the 4 AB_12345 and 3 AB_13579.

Thanks

[Travis_Ratliff · Accepted solution]

I have figured it out was just having a rough morning. I joined the two sets on ID and quantity then use the left join to join back with the original and use a unique filter on the middle join results and got my intended 7 transactions