Alteryx Designer Knowledge Base

Definitive answers from Designer experts.

Alteryx for Excel Users: Find x smallest/largest number in an column

WayneWooldridge
Alteryx
Alteryx
Created

You may already know how to use the MIN() and MAX() functions to find the smallest and largest values in a list. But what if you needed the second smallest number or 3rd largest number in the list?

Excel has a function for this. Using the =SMALL function, you would specify the data range followed by 'x' smallest number you want to find. In the example below, we find the 2nd smallest value in a list:

Find x smallest largest number 01.png

Similarly, if you want the find the 'x' largest value in a list, you would use the =LARGE function. Here we use the =LARGE function to find the 3rd largest value in our list:

Find x smallest largest number 02.png

Let's look at how we do the same thing using Alteryx. We'll start with thesame array of numbers using a Text Input Tool:

Find x smallest largest number 03.png

We want to find the 2nd smallest value in the list. We'll start by sorting the list in ascending order. Then we assign a record id to each row of data. Filter to select record id = 2 and use a Select Tool to drop the record id field (we don't need it in our final result) and that will leave us with our answer, '6'.

Find x smallest largest number 04.png

Find x smallest largest number 05.png

To find the 3rd largest value in the list, we simply change the sort order to descending and filter to select record id = 3:

Find x smallest largest number 06.png

Our result is '13', so everything checks out.

Find x smallest largest number 07.png

We replicated in Alteryx the Excel functions =SMALL and =LARGE. But let's add a couple of bells and whistles to our workflow and make it an app. This gives a user the ability to decide if they want to select the smallest or largest value from our list as well was what the value of 'x' will be. Begin by bringing a Drop Down Tool to the canvas.

Find x smallest largest number 08.png

Enter the text or question to be displayed ('Return smallest/largest value:). Under 'List Values' we'll choose 'Manually set values' and under 'Properties' enter:

Smallest Value:Ascending

Largest Value:Descending

Connect the Drop Down to the lightning bolt on top of the Sort Tool. Automatically an Action Tool will be inserted between the Drop Down and Sort Tools.

Find x smallest largest number 09.png

In the Action configuration widow, select @order - value = "Ascending".

Find x smallest largest number 10.png

Bring another Drop Down Tool to the canvas and enter the test or question to be displayed ('Enter Nth smallest/largest value:'):

Find x smallest largest number 11.png

Connect the Drop Down Tool to the lightning bolt on top of the Filter tool. An Action Tool will automatically be inserted between the Drop Down and Filter Tools:

Find x smallest largest number 12.png

Notice the expression in the Filter Tool is set up to send record id = 1 to the 'T' (true) output side of the tool. In the Action configuration window, select 'Expression - value = "[RecordID] = 1" and enter '1' under 'Replace a specific string:' located at the bottom of the configuration window.

Find x smallest largest number 13.png

Find x smallest largest number 14.png

Optional: Add an Output Data Tool to the end of the workflow so the results can be displayed. In this example, we will be exporting results to a temp html file:

Find x smallest largest number 16.png

So the complete workflow/app looks like this:

Find x smallest largest number 15.png

Let's run the app. Under the main menu and to the right of the run icon, click on the wand:

Find x smallest largest number 17.png

A window will pop up displaying the drop down menus you've setup with the Drop Down Tools. Let's find the 2nd smallest number in our column of numbers:

Find x smallest largest number 18.png

Click 'Finish' and a 'App Results' window pops up. Click on 'OK':

Find x smallest largest number 19.png

Our temp html files returns the value of 6 which is correct.

Find x smallest largest number 20.png

You now have an app other users can use to easily and quickly select the'x' smallest or largest value in a list of numbers. To learn more about apps and interface tools in general, see here.

Attachments
No ratings
Comments
SeanAdams
17 - Castor
17 - Castor

great article @WayneWooldridge  - one of the great parts about Alteryx is that you always have multiple ways to solve the same problem - in this case, you can also use the Select Records tool instead of the recordID with a filter.

 

Thank you also for rounding this out by wrapping this in an app - this is an area of Alteryx which is under-appreciated 🙂