We have a dataset where we have three friends SLC, Kim, Tom, and Hanks. And out of these four SLC has the responsibility to get fruits. But in case if any of the other friends get the fruits then we have to remove them from
Prd_Id | Name | Type | Price |
123 | SLC | Fruit | 125.01 |
345 | Kim | Vegetables | 1457.00 |
123 | Tom | Fruit | 145.01 |
123 | Hanks | Fruit | 4527 |
456 | SLC | Fruit | 124.01 |
890 | SLC | Fruit | 126.01 |
159 | Tom | Fruit | 457.01 |
890 | Kim | Fruit | 523 |
Condition:
1. I have to check if the same product id and type is part of the sheet-> if they do I have to remove them
2. I duplicate was found then we have to select the record which has SLC
Example:
Product id -> 123 - SLC- fruit is also present for tom and hank so I have to remove them
another example: product id -> 159 - Tom - Fruit -> As SLCdoes have the same product id and type in the sheet so we have to include this
Output:
Prd_Id | Name | Type | price |
123 | SLC | Fruit | 125.01 |
345 | Kim | Vegetables | 1457.00 |
456 | SLC | Fruit | 124.01 |
890 | SLC | Fruit | 126.01 |
159 | Tom | Fruit | 457.01 |
Let me know if any confusion
Hey @Sshasnk ,
Assuming I understand the requirements correctly, you can do what you are looking to achieve with a summarize tool
Hope I understand correctly and this helps.
Cheers,
Angelos
Sort by Product Id, Type, and Name: this will put Alex at the top. You can create a separate mapping table for a custom sort order, if necessary.
Then use the Unique tool, unique values on Product Id and Fruit. Take the output from the U anchor. The duplicates will be in the D anchor.
Chris
Please don't go by alphabetical order because I have created this as sample data, In a real scenario, it is SLC instead of Alex.
Also, I cant use summarize tool because I have more than 30 fields in the dataset
You can use Text Input to create a custom table, columns for Field Name and Sort Order, add SLC to the first column and 1 to the 2nd column.
Join from your data file to the custom table to get the Sort Order field. Then sort by the custom sort order before using the Unique tool. Try it.
Chris