This week, our cohort has a variety of challenges, with no two people getting the same question. The one thing we all have in common is using Alteryx to tackle our individual challenges. We’ve got about two hours to work on our solutions and present them to our cohort and others in the Teams meeting room.
For Challenge #337, my task was to create a new list from an existing one. The hardest part for me was figuring out the best tools and functions to use for an efficient solution. I decided to break down the challenge into two main steps:
1. Find all movies tagged as international from a column with multiple tags for each movie.
I started by splitting the column into four new columns using a comma as the delimiter in the ‘Text to Columns’ tool. This was to see if this number of columns would cover the maximum number of genres. It turned out that the most genres any movie had in the dataset was three.
Next, I used the ‘Filter’ tool to pick out all the movies tagged as international from one of the split genre columns.
2. Combine the columns that don’t have the international movie tag to create a new genre column.
3. Reflection and User Experience:
While I managed to get the results I was aiming for (as shown in the picture below), there’s room for improvement.
I tried to split the ‘listed_in’ column into four new columns at the beginning, and it worked well in this scenario. However, this approach will not be as effective with large datasets where many genres are stored in a single column. I would then have to try multiple times to get the right number of split columns. In the future, instead of manually splitting columns, I’ll find the maximum number of delimiters and update my ‘Text to Columns’ tool dynamically using the following formula.
In the meantime, with the ‘Find and Replace’ tool, I can easily locate all records containing the ‘International Movies’ tag in the ‘listed_in’ column and either remove or replace these tags directly. This method streamlines the analysis process by eliminating the need for the previous steps of splitting and combining columns.
In Alteryx’s workflow-based environment, the variety of tools available allows different users to employ various methods to achieve the same outcome. There is no single ‘best’ solution; instead, the most suitable solution depends on the nature of the analysis and individual user preferences. Users can always adjust the order and syntax of each tool in Alteryx to better meet their analytical needs.
