please assist on the below . i have used join tool **join by specific records but it created duplicated rows and when we apply unique tool its deleting 3000 with 25775 (table A) instead of 34058. its giving invalid difference of 8283. output received : Product IDQuantityAmountDistributorRight_Product IDTransaction IDRight_Quantitysale AmountDistributor/AgentDifference671910000146843LG671911008910000146843LGEC067191100025260SAMSUNG67191234561100025260SAMSUNGEC06719300034058LG6719220569300025775LGEC82836719300034058LG6719231589300034058LGEC067196000597LG67197189566000597LGEC0 input: Table A Product IDQuantityAmountDistributor6719 11,000 25,260SAMSUNG6719 3,000 34,058LG6719 10,000 146,843LG6719 3,000 25,775LG6719 6,000 597LG Table B Product IDTransaction IDQuantitysale AmountDistributor/Agent67191234561100025260SAMSUNGEC67197189566000597LGEC671911008910000146843LGEC6719231589300034058LGEC6719220569300025775LGEC Required Output: Difference = Amount - Sale Amount Product IDQuantityAmountDistributorProduct IDTransaction IDQuantitysale AmountDistributor/AgentDifference6719 11,000 25,260SAMSUNG67191234561100025260SAMSUNGEC06719 3,000 34,058LG6719231589300034058LGEC06719 10,000 146,843LG671911008910000146843LGEC06719 3,000 25,775LG6719220569300025775LGEC06719 6,000 597LG67197189566000597LGEC0

Reconciliation of two tables with Join tool (resolve duplicate records)

Anjankumar2021

please assist on the below . i have used join tool **join by specific records but it created duplicated rows and when we apply unique tool its deleting 3000 with 25775 (table A) instead of 34058. its giving invalid difference of 8283.

output received :

Product ID	Quantity	Amount	Distributor	Right_Product ID	Transaction ID	Right_Quantity	sale Amount	Distributor/Agent	Difference
6719	10000	146843	LG	6719	110089	10000	146843	LGEC	0
6719	11000	25260	SAMSUNG	6719	123456	11000	25260	SAMSUNGEC	0
6719	3000	34058	LG	6719	220569	3000	25775	LGEC	8283
6719	3000	34058	LG	6719	231589	3000	34058	LGEC	0
6719	6000	597	LG	6719	718956	6000	597	LGEC	0

input:

Table A
Product ID	Quantity	Amount	Distributor
6719	11,000	25,260	SAMSUNG
6719	3,000	34,058	LG
6719	10,000	146,843	LG
6719	3,000	25,775	LG
6719	6,000	597	LG

Table B
Product ID	Transaction ID	Quantity	sale Amount	Distributor/Agent
6719	123456	11000	25260	SAMSUNGEC
6719	718956	6000	597	LGEC
6719	110089	10000	146843	LGEC
6719	231589	3000	34058	LGEC
6719	220569	3000	25775	LGEC

Required Output: Difference = Amount - Sale Amount

Product ID	Quantity	Amount	Distributor	Product ID	Transaction ID	Quantity	sale Amount	Distributor/Agent	Difference
6719	11,000	25,260	SAMSUNG	6719	123456	11000	25260	SAMSUNGEC	0
6719	3,000	34,058	LG	6719	231589	3000	34058	LGEC	0
6719	10,000	146,843	LG	6719	110089	10000	146843	LGEC	0
6719	3,000	25,775	LG	6719	220569	3000	25775	LGEC	0
6719	6,000	597	LG	6719	718956	6000	597	LGEC	0

Workflow

Accepted answers

binu_acs

@Anjankumar2021

Reconcillation_23052024.yxmd

binu_acs

@Anjankumar2021

Reconcillation_23052024.yxmd

binu_acs

@Anjankumar2021 I had to add the agent also in the logic to match your output

Reconcillation_08062024.yxmd

All comments

flying008

Hi, @Anjankumar2021

FYI.

录制_2024_05_23_15_00_30_743.gif

binu_acs

@Anjankumar2021

Reconcillation_23052024.yxmd

Anjankumar2021

thank you so much for your help

Anjankumar2021

@binu_acs workflow unable to reconcile for the below . we got invalid break of 2491 and 2489. could you pls advise in this case.

1	6719	11000	25260	SAMSUNG	123456	11000	25260	SAMSUNGEC	0
2	6719	3000	34058	LG	231589	3000	34058	LGEC	0
3	6719	10000	146843	LG	110089	10000	146843	LGEC	0
4	6719	3000	25775	LG	220569	3000	25775	LGEC	0
5	6719	6000	597	LG	718956	6000	597	LGEC	0
6	6732	2000	402	SAMSUNG	171115420	2000	2891	LGEC	-2491
7	6732	2000	2891	LG	171166590	2000	402	LGEC	2489
8	6732	3000	831	LG	169975342	3000	831	SAMSUNGEC	0

input :

table 1

Product ID	Quantity	Amount	Distributor
6719	11,000	25,260	SAMSUNG
6719	3,000	34,058	LG
6719	10,000	146,843	LG
6719	3,000	25,775	LG
6719	6,000	597	LG
6732	2,000	400	SAMSUNG
6732	2,000	2,891	LG
6732	3,000	831	LG

table2:

Product ID	Transaction ID	Quantity	sale Amount	Distributor/Agent
6719	123456	11000	25260	SAMSUNGEC
6719	718956	6000	597	LGEC
6719	110089	10000	146843	LGEC
6719	231589	3000	34058	LGEC
6719	220569	3000	25775	LGEC
6732	169975342	3000	831	SAMSUNGEC
6732	171115420	2000	2891	LGEC
6732	171166590	2000	402	LGEC

required output:

RecordID	Product ID	Quantity	Amount	Distributor	Transaction ID	Right_Quantity	sale Amount	Distributor/Agent	diff
1	6719	11000	25260	SAMSUNG	123456	11000	25260	SAMSUNGEC	0
2	6719	3000	34058	LG	231589	3000	34058	LGEC	0
3	6719	10000	146843	LG	110089	10000	146843	LGEC	0
4	6719	3000	25775	LG	220569	3000	25775	LGEC	0
5	6719	6000	597	LG	718956	6000	597	LGEC	0
6	6732	2000	400	SAMSUNG	171115420	2000	402	LGEC	2
7	6732	2000	2891	LG	171166590	2000	2891	LGEC	0
8	6732	3000	831	LG	169975342	3000	831	SAMSUNGEC	0

binu_acs

@Anjankumar2021

Reconcillation_23052024.yxmd

Anjankumar2021

Amazing its working good...:)

Anjankumar2021

@binu_acs Kindly assist on the below exception.

workflow working fine and giving 98% accuracy. but got below exception with Distributor/Agent.

when reconciling Distributor or agent name should match with Product id , Qty, amount / sales amount.

workflow is able to match all above fields except distributor / agent.

LG Vs. SAMSUNG

SAMSUNG Vs LG

Input files:

Table A				Table B
Product ID	Quantity	Amount	Distributor	Product ID	Transaction ID	Quantity	sale Amount	Distributor/Agent
9999	2000	538	LG	9999	13267154	105000	300	TCL
9999	7000	1541	LG	9999	81562938	20000	2200	Onida
9999	3000	605	LG	9999	18205350	12000	850	Onida
9999	4000	531	LG	9999	18229595	2000	80	Onida
9999	9000	513	LG	9999	18236437	5000	170	Onida
9999	2000	92	LG	9999	11557439	86000	300	TCL
9999	26000	1421	LG	9999	18167945	2000	565	LG
9999	1000	30	LG	9999	171861997	7000	1565	LG
9999	5000	510	Sansui	9999	18225328	3000	622	LG
9999	2000	97	Samsung	9999	18291976	4000	550	LG
9999	2000	88	Samsung	9999	18313208	9000	520	LG
9999	11000	277	Samsung	9999	13137908	2000	100	LG
9999	20000	2198	Onida	9999	83212521	26000	1426	LG
9999	12000	845	Onida	9999	83477386	1000	31	LG
9999	2000	69	Onida	9999	81685008	5000	536	Sansui
9999	5000	174	Onida	9999	18275687	2000	97	Samsung
9999	86000	303	TCL	9999	12886183	2000	88	Samsung
9999	105000	300	TCL	9999	73273257	11000	277	Samsung

we got output as per below.

RecordID	Product ID	Quantity	Amount	Distributor	Transaction ID	Right_Quantity	sale Amount	Distributor/Agent	diff
1	9999	2000	538	LG	18167945	2000	565	LG	-27
2	9999	7000	1541	LG	171861997	7000	1565	LG	-24
3	9999	3000	605	LG	18225328	3000	622	LG	-17
4	9999	4000	531	LG	18291976	4000	550	LG	-19
5	9999	9000	513	LG	18313208	9000	520	LG	-7
6	9999	2000	92	LG	18275687	2000	97	Samsung	-5
7	9999	26000	1421	LG	83212521	26000	1426	LG	-5
8	9999	1000	30	LG	83477386	1000	31	LG	-1
9	9999	5000	510	Sansui	81685008	5000	536	Sansui	-26
10	9999	2000	97	Samsung	13137908	2000	100	LG	-3
11	9999	2000	88	Samsung	12886183	2000	88	Samsung	0
12	9999	11000	277	Samsung	73273257	11000	277	Samsung	0
13	9999	20000	2198	Onida	81562938	20000	2200	Onida	-2
14	9999	12000	845	Onida	18205350	12000	850	Onida	-5
15	9999	2000	69	Onida	18229595	2000	80	Onida	-11
16	9999	5000	174	Onida	18236437	5000	170	Onida	4
17	9999	86000	303	TCL	11557439	86000	300	TCL	3
18	9999	105000	300	TCL	13267154	105000	300	TCL	0

Required output :

RecordID	Product ID	Quantity	Amount	Distributor	Transaction ID	Right_Quantity	sale Amount	Distributor/Agent	diff
1	9999	2000	538	LG	18167945	2000	565	LG	-27
2	9999	7000	1541	LG	171861997	7000	1565	LG	-24
3	9999	3000	605	LG	18225328	3000	622	LG	-17
4	9999	4000	531	LG	18291976	4000	550	LG	-19
5	9999	9000	513	LG	18313208	9000	520	LG	-7
6	9999	2000	92	LG	13137908	2000	100	LG	-8
7	9999	26000	1421	LG	83212521	26000	1426	LG	-5
8	9999	1000	30	LG	83477386	1000	31	LG	-1
9	9999	5000	510	Sansui	81685008	5000	536	Sansui	-26
10	9999	2000	97	Samsung	18275687	2000	97	Samsung	0
11	9999	2000	88	Samsung	12886183	2000	88	Samsung	0
12	9999	11000	277	Samsung	73273257	11000	277	Samsung	0
13	9999	20000	2198	Onida	81562938	20000	2200	Onida	-2
14	9999	12000	845	Onida	18205350	12000	850	Onida	-5
15	9999	2000	69	Onida	18229595	2000	80	Onida	-11
16	9999	5000	174	Onida	18236437	5000	170	Onida	4
17	9999	86000	303	TCL	11557439	86000	300	TCL	3
18	9999	105000	300	TCL	13267154	105000	300	TCL	0

binu_acs

@Anjankumar2021 I had to add the agent also in the logic to match your output

Reconcillation_08062024.yxmd

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15879

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703