SOLVED
Reconciliation of two tables with Join tool (resolve duplicate records)
Options
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
Anjankumar2021
8 - Asteroid
‎05-22-2024
11:35 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
Labels:
- Labels:
- Workflow
8 REPLIES 8
flying008
15 - Aurora
‎05-23-2024
12:01 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
binuacs
21 - Polaris
‎05-23-2024
02:08 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
‎05-23-2024
10:19 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
thank you so much for your help :)
‎05-26-2024
07:57 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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 |
binuacs
21 - Polaris
‎05-27-2024
03:59 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
‎05-27-2024
07:14 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Amazing its working good...:)
‎06-07-2024
12:42 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@binuacs 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 |
binuacs
21 - Polaris
‎06-08-2024
08:44 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@Anjankumar2021 I had to add the agent also in the logic to match your output
