11-15-2016 03:01 PM - edited 08-03-2021 04:09 PM
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:
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:
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:
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'.
To find the 3rd largest value in the list, we simply change the sort order to descending and filter to select record id = 3:
Our result is '13', so everything checks out.
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.
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.
In the Action configuration widow, select @order - value = "Ascending".
Bring another Drop Down Tool to the canvas and enter the test or question to be displayed ('Enter Nth smallest/largest value:'):
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:
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.
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:
So the complete workflow/app looks like this:
Let's run the app. Under the main menu and to the right of the run icon, click on the wand:
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:
Click 'Finish' and a 'App Results' window pops up. Click on 'OK':
Our temp html files returns the value of 6 which is correct.
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.
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 🙂