community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx Designer Knowledge Base

Definitive answers from Designer experts.

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

Alteryx
Alteryx
Created on

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 the same 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