Selecting largest records in a table

[anamik72]

Please find below an input and an output table. I want output table to include only those rows from input table where Amount is largest for a given Customer. Can you help? Thanks

Customer	Channel	Sub Customer	Amount
111	Channel A	123	$           1,000
111	Channel B	345	$                 10
111	Channel C	456	$                   5
222	Channel A	678	$               780
222	Channel B	345	$               560
222	Channel C	999	$               670

Output
Customer	Channel	Sub Customer	Amount
111	Channel A	123	$           1,000
222	Channel A	678	$               780

[Aaron_Harter · Accepted solution]

Hi @anamik72,

First use a Summarize tool to determine the Max value per Customer, then use a Join to filter the data table for only those records:

 

[grossal]

Hi @anamik72,

you can do this using a combination of the Sort and Sample Tool.

If the data is already sorted (as in your example), you wouldn't need the Sort tool. In your case, I also had to clean up the Amount to get a clear number and sort it properly. If you want to preserve the original value, you could add a join behind it.

I have attached a workflow that includes both ways. Let me know what you think.

Best

Alex

[anamik72]

This is perfect. Thanks a bunch.