Top/Bottom "N" Tool

[NicoleJohnson]

Would love to see a tool that allows you to find the Top N or Bottom N% etc. using a single tool, rather than the current common practices of using 2-3 tools to accomplish this simple task. It's possible some/all of this functionality could be added by simply expanding the current Sample tool to include more options, or at least mirroring the configuration of the Sample Tool in the creation of a new "Top/Bottom Tool."

For example, let's say I wanted to find the top 5 student grades, and then compare all scores to those top 5 grades. I would currently need to do something along the lines of Sort descending (and/or Summarize Tool, if grouping is needed) + Sample Tool (First N Records) + Join the results back to the data. That's anywhere from 3-4 tools to accomplish a simple task that could potentially be done with 1-2.

I'm envisioning this working somewhat like the Top/Bottom rules in Excel Conditional Formatting (see below), and similar to some of the existing options in the Sample Tool (also see below). For example, rather than only being able to select the First N Records in the Sample Tool, I could indicate that I want to select the Top N Records, or the Bottom N% Records. This would prevent the additional step of having to group/sort your data before using the Sample Tool, especially in cases where you're then having to put your records back into their original order rather than leaving them in their grouped/sorted state. You'd still want to have the option of choosing grouping fields if desired. You would also need to have a drop-down field to indicate which field to apply the "Top/Bottom rules" to.

    

A list of potential "Top/Bottom" options that I believe would be great additions include:

• Top N
• Bottom N
• Top N %
• Bottom N %
• Above Average
• Below Average
• Within a Percentile Range (i.e. "Between 20-30%")
• Skip Top N
• Skip Bottom N

The value added with just the options above would be huge in helping to streamline workflows and reduce unnecessary tools on the canvas.

[SeanAdams]

Hey @NicoleJohnson,

If we could extend this slightly to include an ordering clause it would be great.

"please give me top n, order by column X, group by column Y & Z"

Example

ID        Version         Value

1          1                   Dog

1          2                   Bird

1          3                   Big Bird

2          1                   Dog

2          2                   Cat

3          1                   Dog

If we run this to return top 1, ordered by Version Number Descending, grouping on ID, then the result would be:

ID        Version         Value

1          3                   Big Bird

2          2                   Cat

3          1                   Dog

As you say in your suggestion - this currently takes quite a few tools to achieve.

[Fgill]

Why not create your own macro?

It seems to me the paradigmn is to keep tools to a single function, which I love.  And Alteryx does what you need, a macro could solve this for you easily.

[TrucT_experian]

This is a simple enhancement that can really help.