Hi,
Below is my sample data set. If any of the rows of a Product ID contain a negative (-) Amount, I want to see all the rows of that Product ID (only). Any help would be highly appreciated. Thanks.
Input Data:
Product ID | Product Name | Amount |
1001001 | Laptop | -3000 |
1001001 | Laptop | 3000 |
1001001 | Laptop | 4800 |
1001002 | Keyboard | 1230 |
1001002 | Keyboard | 1500 |
1001003 | Mouse | -27800 |
1001003 | Mouse | 7000 |
1001003 | Mouse | 27800 |
1001003 | Mouse | 80 |
1001003 | Mouse | 1100 |
1001004 | TV | -100 |
1001004 | TV | 1000 |
1001005 | PC | 200 |
1001005 | PC | 250 |
1001006 | Tablet | 200 |
1001006 | Tablet | 150 |
1001007 | Smart Phone | 500 |
1001007 | Smart Phone | -450 |
Desired Output:
Product ID | Product Name | Amount |
1001001 | Laptop | -3000 |
1001001 | Laptop | 3000 |
1001001 | Laptop | 4800 |
1001003 | Mouse | -27800 |
1001003 | Mouse | 7000 |
1001003 | Mouse | 27800 |
1001003 | Mouse | 80 |
1001003 | Mouse | 1100 |
1001004 | TV | -100 |
1001004 | TV | 1000 |
1001007 | Smart Phone | 500 |
1001007 | Smart Phone | -450 |
Solved! Go to Solution.
I would approach this by using a Filter, Summarize, and Join tool.
- Filter records that contain a negative value
- Summarize the Product Names from that list
- Join that list of Product Names to the original and the Joined result will be your requested output.
*The Join tool will sort the data based on the joining field(s), so a sort tool might be useful to return the original Product ID order.
Check out the attached workflow to see this in action.
Hi @Muhammad I mocked up an example on how you could do this.