In this series, Liz Jensen tackles specific cases from the Alteryx Weekly Challenge initiative. She wades through the countless tools and options available in Alteryx and shares her problem-solving process along the way.
In this second installment of Alter Your Analysis, I am tackling Alteryx’s Weekly Challenge 169: “Using the values in the attached file womens_world_cup_data.txt, determine which team won the most matches.”
A key point: We’re trying to determine the most matches won, not the most finals (go Team USA!).
Breaking the Challenge into Tools
Looking at the text file that contained our data, I had a little legwork to do in order to determine who our winningest team was. As I mentioned in my last blog post, I like to approach each workflow in Alteryx by breaking it down into smaller steps. These smaller steps will determine which tools I use. Here’s what I needed to do to get my answer, and the tools I used to get there:
- Connect to the data (of course!). I used an INPUT tool here.
- I wanted to FILTER out any ties. We just cared about wins, so I kept only matches where Team I’s scores were NOT the same as Team J. Using just the False output, I was able to look at matches where there were Winners and Losers.
- Then I used a FORMULA tool to determine who my winning Team was—if Score I was greater than Score J, then return Team I. Otherwise, return Team J. This would be my winning team.
- After I had the winning team for each match, I needed to use a SUMMARIZE tool to count how many times a team won a match. This is where it got interesting. I not only had to count how many times a team won (Count the Winning Team), but I also had to group by the winning team. Essentially, I am using the Winning Team twice in this SUMMARIZE Otherwise, it will just output how many winning teams I had.
- Next, I used a SORT tool to put our higher counts up top.
- Finally, I used a SAMPLE tool to just return the top most-winning team. Looks like it’s England with 16 matches!
You can check out my workflow below:
While there is no prize for the shortest or prettiest workflow, as my Alteryx trainer Steph Kennedy pointed out, I am quite pleased that I was able to accomplish this challenge with only six tools. While not necessarily faster, more complicated workflows can be tedious to troubleshoot.
Thanks for reading, and congratulations to everyone in the Women’s World Cup but especially the USA, 2019 World Champions!