Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Selecting largest records in a table

anamik72
8 - Asteroid

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

 

CustomerChannelSub CustomerAmount
111Channel A123 $           1,000
111Channel B345 $                 10
111Channel C456 $                   5
222Channel A678 $               780
222Channel B345 $               560
222Channel C999 $               670

 

Output   
CustomerChannelSub CustomerAmount
111Channel A123 $           1,000
222Channel A678 $               780
3 REPLIES 3
Aaron_Harter
11 - Bolide

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:

5.PNG 

grossal
15 - Aurora
15 - Aurora

Hi @anamik72,

 

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

 

grossal_0-1587073194875.png

 

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.

 

grossal_1-1587073345900.png

 

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

 

Best

Alex

anamik72
8 - Asteroid

This is perfect. Thanks a bunch.

Labels