Blogs / Self-Service BI / Alteryx / Fetching Top N Records in Alteryx

Fetching Top N Records in Alteryx

Aug 15, 2019

SHARE

In a large dataset, when you want to focus on certain data you can apply filtering/ aggregating/ fetching top or bottom data. Alteryx provides an enormous tool to achieve this data preparation.

In our previous blog, we solved one of the weekly challenges to demonstrate the usage of tools like transpose, crosstab, sorting, sampling.

In this blog, we have taken one business case and explained the steps involved to solve it in Alteryx.

Requirement

Let’s say the CEO of a company wants to know which are the Top 3 Products that are performing well and in which countries they are performing (i.e., top 3 countries).

Steps of the workflow

Step 1: Get the input

The Text Input tool will have the details of country-wise product sales.

Fetching Top N Records in Alteryx

Step 2: Find Top 3 Countries by sales

2.1 Required fields selection

Select Tool is used to restrict the data to only Country and its Sales amount.

Fetching Top N Records in Alteryx

2.2 Aggregate Sales

By default, the select tool won’t aggregate the input data. So, Summarize Tool is used to aggregate Sales amount by Country. It is used to filter input data by selecting the required fields to increase the workflow’s performance.

Fetching Top N Records in Alteryx

2.3 Find Top 3 countries

Use the Sort tool to arrange the Sales value in descending order. Further, use the Sampling tool to pick the top 3 sorted records.

Fetching Top N Records in Alteryx

The output of Step 2 looks like this:

Fetching Top N Records in Alteryx

In Alteryx Designer, we use various tools for different operations that are later organized into executable workflows. Tool Container is the component that is used to group tools in the workflow. This will include/exclude a set of tools from the workflow execution.

Fetching Top N Records in Alteryx

To get this tool container:

  • Select the required tool -> Right-click -> In the context menu, choose the Tool Container option.
  • Type the tool name in the search box and select it

Note: We will use this tool in upcoming steps to group the tools.

Step 3: Find Top 3 Products from each country

Arrange Sales amount in descending order using the Sort tool. Specifying the top 3 values in Sampling tool gives the top 3 records alone as the output. What we finally need is the top 3 Products in each country.  This can be accomplished by leveraging Group by column option in the Sampling tool. Check the configuration below:

Fetching Top N Records in Alteryx

From steps 2 and 3, we now have the top 3 countries and the top 3 products for each country.

4. Find Intersection

To achieve our requirement, we must find the Intersection of these two results. To do this, we can use the Join tool in Alteryx as shown below.

Usually Join tool will have three outputs namely, L anchor, R anchor, J anchor. Here we have joined step 2 & 3 outputs as Inner Join.

Fetching Top N Records in Alteryx

Solution

Fetching Top N Records in Alteryx

Final Output

Fetching Top N Records in Alteryx

By this we have achieved our business case of finding the top 3 Sales from top 3 Countries while exploring the features of Alteryx tools such as Select, Join, Sorting, and Sampling.

Read more about similar Self Service BI topic here and learn more about Visual BI Solutions Microsoft Power BI offerings here. 


Corporate HQ:
5920 Windhaven Pkwy, Plano, TX 75093

+1 888-227-2794

+1 972-232-2233

+1 888-227-7192

solutions@visualbi.com


Copyright © Visual BI Solutions Inc.

Subscribe To Our Newsletter

Subscribe To Our Newsletter

Join our mailing list to receive the latest news and updates from our team.

You have Successfully Subscribed!

Share This!

Share this with your friends and colleagues!