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 |
Solved! Go to Solution.
thank you so much for your help :)
@binuacs 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 |
Amazing its working good...:)
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 |
@Anjankumar2021 I had to add the agent also in the logic to match your output