Reconciliation of Two tables

[Anjankumar2021]

Hi Team,

Please assist on below issue.

i have used join tool to combine two tables with Product ID and Quantity but i got duplicated rows. so i used unique with transaction id.

Hence i received wrong difference as per below.

i want to find difference between amount and sales amount.

**Difference = amount (table A) - Sales amount (table B)

Actual output: we receive after running workflow:

Product ID	Quantity	Amount	Distributor	Right_Product ID	Transaction ID	Right_Quantity	sale Amount	Sale date	Distributor/Agent	Difference
6719	10000	146843	LG	6719	110089	10000	146843	12/12/2022	LGEC	0
6719	11000	25260	SAMSUNG	6719	123456	11000	25260	13/07/2023	SAMSUNGEC	0
6719	3000	34058	LG	6719	220569	3000	25775	15/03/2023	LGEC	8283
6719	3000	34058	LG	6719	231589	3000	34058	26/10/2022	LGEC	0
6719	6000	597	LG	6719	718956	6000	597	15/08/2023	LGEC	0

Inputs:

Input					Input
Table A					Table B
Product ID	Quantity	Amount	Distributor		Product ID	Transaction ID	Quantity	sale Amount	Sale date	Distributor/Agent
6719	11,000	25,260	SAMSUNG		6719	123456	11000	25260	13/07/2023	SAMSUNGEC
6719	3,000	34,058	LG		6719	231589	3000	34058	26/10/2022	LGEC
6719	10,000	146,843	LG		6719	110089	10000	146843	12/12/2022	LGEC
6719	3,000	25,775	LG		6719	220569	3000	25775	15/03/2023	LGEC
6719	6,000	597	LG		6719	718956	6000	597	15/08/2023	LGEC

Expected Output::

Product ID	Quantity	Amount	Distributor	Product ID	Transaction ID	Quantity	sale Amount	Sale date	Distributor/Agent	Difference
6719	11,000	25,260	SAMSUNG	6719	123456	11000	25260	13/07/2023	SAMSUNGEC	0
6719	3,000	34,058	LG	6719	231589	3000	34058	26/10/2022	LGEC	0
6719	10,000	146,843	LG	6719	110089	10000	146843	12/12/2022	LGEC	0
6719	3,000	25,775	LG	6719	220569	3000	25775	15/03/2023	LGEC	0
6719	6,000	597	LG	6719	718956	6000	597	15/08/2023	LGEC	0

[rzdodson]

@Anjankumar2021 maybe something like this? I joined on Record Position and then calculated the difference between Quantity fields. Hope that helps! :)

[Anjankumar2021]

i want to find difference between amount and sales amount.

Difference = Amount (Table A) - Sales amount(Table B).

Please assist

[rzdodson]

You would just change the calculation in the formula tool.

If this solves the use case case, please accept as a solution to help future Alteryx users :)