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.

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.

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.

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.

The output of Step 2 looks like this:

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.

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:

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.

Solution

Final Output

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.