Hi -
I have the following situation - I have some duplicate rows (for example, the first two in this sample set), I want to keep only the row where the PartTypeValue is "MPS":
Name | SiteID | PartTypeValue | Min_Warehouse |
00-011085 | 300 | MPS | WH3000 |
00-011085 | 300 | Make | WH3000 |
20402 | 100 | Make | USA102 |
20402 | 110 | Make | WH1101 |
20402 | 112 | MPS | WH1008 |
20402 | 112 | Make | WH1008 |
20402 | 113 | Make | USA204 |
20402 | 114 | Make | WH1009 |
20402 | 120 | Make | USA101 |
20402 | 200 | MPS | USA201 |
20402 | 300 | Make | USA301 |
20402 | 400 | Make | IRL101 |
What is needed is a single record (unique to Name and SiteID) - keeping the MPS record when applicable:
Name | SiteID | PartTypeValue | Min_Warehouse |
00-011085 | 300 | MPS | WH3000 |
20402 | 100 | Make | USA102 |
20402 | 110 | Make | WH1101 |
20402 | 112 | MPS | WH1008 |
20402 | 113 | Make | USA204 |
20402 | 114 | Make | WH1009 |
20402 | 120 | Make | USA101 |
20402 | 200 | MPS | USA201 |
20402 | 300 | Make | USA301 |
20402 | 400 | Make | IRL101 |
Not sure how to "remove" the second, non-MPS record in those instances were I have two records for a unique Name-SiteID combination.
thanks - john
Solved! Go to Solution.
Hello @john_armentrout
Will it always be MPS or Make? If yes, you can sort the data by Name, then by PartTypeValue in a descending order then add a Unique tool to remove duplicates based on Name and Site ID like below.
it will always be MPS, if there is an MPS row - otherwise, it would be Make (there is another value, "Buy", that is treated the same way - MPS overrides both, otherwise whatever the other value is). Let me try what you suggested...
Hi @john_armentrout ,
If there are other possible values for PartTypeValue you could use a join and filter in addition to the unique tool to make sure you always get MPS:
as expected, the approach works for MPS and Make - but not for MPS and Buy. In my 15000 rows, I have examples of "duplicates" with MPS and Make, and MPS and Buy. always need MPS to win...
@Chantelb Thanks - that seemed to do it. I'm validating, but thanks much!
@Chantelb - validated,thanks again