1. Joins in Alteryx
Let's look at how to use the Join tool in Alteryx.
In this scenario, we aim to generate a sorted list of customers along with their information, ranking them according to the total number of orders they have placed over time.
Let's begin by importing the customer tables. We're dealing with three distinct tables, each representing customers from one of our three regions.
Given that the structure of these tables is identical and their naming conventions are similar, except for the suffix, we can leverage a shortcut that Alteryx offers: the wildcard import. This method involves directing our input tool to a specific folder, inputting the common part of the filenames we wish to load, and substituting an asterisk for the varying segment of the filename. This allows for efficient and streamlined loading of multiple files with similar structures.
Upon executing the workflow, we observe that it successfully unions the three customer tables for a total of 1,445 records.
Now let’s do the same for the orders tables.
Although a powerful tool, use the wildcard import with caution, as it scans the entire folder for any filenames that match the specified pattern.
Before linking our customer table with the orders table, let’s summarize the count of unique orders per customer.
While this aggregation could technically be performed after the join, it's a best practice to execute it beforehand due to significant performance benefits. This approach ensures more efficient data processing and reduces the computational load. We'll explore performance optimization techniques in greater detail in the last chapter of this course, so stay tuned for more insights!
Now that we have the customer list and the count of orders by customer, it's time to join the two datasets. Considering our interest lies only in customers who have placed orders, an inner join is the appropriate choice for this operation. With customers being our primary focus, we will position the customer table on the left input and the aggregated orders table on the right input. This setup ensures that we only include customers who have made purchases, aligning with our analysis goal to focus on active customers.
Within the join tool, we'll configure it to join records based on the customer_id, which serves as the primary key in our customer table and a foreign key in the orders table. To eliminate redundancy, we'll utilize the "deselect all duplicate fields" option from the dropdown menu. This action ensures the removal of the redundant customer_id field from the orders table, leading to a cleaner and more efficient dataset.
In the join tool's configuration, we observe three distinct output anchors: L, J, and R. The L output contains records from the left input that did not match any record from the right input, essentially representing unmatched left-side data. The J output houses records that successfully joined, indicating a match between the left and right inputs. Lastly, the R output includes records from the right input that found no corresponding match in the left input, showcasing unmatched right-side data.
To effectively construct a Left, Right, or Full join in Alteryx, we can strategically direct these outputs to a Union tool. For example, for a Left join, we would combine the L and J outputs, ensuring all records from the left input are included, regardless of a match.
For this specific request, where the focus is solely on matching records, we will utilize only the records from the J output anchor of the join tool. This effectively creates an inner join, ensuring that the final dataset contains only records that have a match in both tables.
The final step involves sorting the list by the count of distinct orders in descending order. Following the Sort tool, we'll add a Browse tool and run the workflow.
We now have a comprehensive list of customers along with their relevant information, ranked by the number of orders they have placed.
Now it’s your turn to start using joins in Alteryx!
2. Let's practice!