Overview
You are working for the Utah Golf Association as a data specialist. You have been provided with a .csv file that contains detailed data regarding the top 25 players in the league and their performance in each event they competed in during the 2022 season. When a player performs well, they are awarded points that contribute toward their overall ranking. Thus, a player with more points will be ranked higher than a player with less points. It is time to name the “Player of the Year” and your supervisor has asked you to create the final leaderboard based on player point totals. Before heading out to lunch, your supervisor reminds you that, for each player, only their ten best events count toward their final rankings.
Instructions
Your supervisor would like to have two PDF documents with the following information ready to review after lunch:
The top 25 leaderboard containing four columns: Position, Player, Total Points, and Points Behind Leader.
a. Remember to only include the highest ten-point totals for each player.
b. Sort the leaderboard by ascending position (1-25).
c. This will be presented in a table format.
A table with five columns: Position, Player, Number of Events, Total Points, and Average Points Per Event.
a. Maintain the same ordering as the final leaderboard table.
b. Average Points Per Event should be rounded to two decimal places.
Remember, this is being presented to your supervisor. A little time spent formatting the final PDF documents will go a long way to impress!
I had fun with this one! I’m not super familiar with the pdf integration but your video was helpful to learn how to do this in the future. My workflow looks a little different, and I think it’s because I assumed the second table we were making also used the top 10 events (related to points). Maybe you could make that more clear in the instructions? Great challenge!
Great challenge! The only significant difference we had in the solution was using the Multi-Row Formula tool instead of the Tile tool. I liked it because it cuts out the extra step of using the select tool to rename/remove a column. Challenge5_Solution.yxmd (34.1 KB)
Great challenge! I did it a little differently than you but it appears that we got the same answer. I enjoyed outputting my solution into a pdf, I have never been asked to do that before so I was happy to learn that. Thanks for putting this together! Here is my solution:
Super cool challenge. I ended up completing it in SQL Server using T-SQL:
--Convert Points to Numeric
IF OBJECT_ID('dbo.Clean_Player_Detail') IS NOT NULL
DROP VIEW dbo.Clean_Player_Detail;
GO
CREATE VIEW dbo.Clean_Player_Detail
AS (
SELECT
Player
, Event
, Awarded_Date
, Position
, ISNULL(TRY_CONVERT(float,Points), CONVERT(float,LEFT(Points,LEN(Points)-1))) AS cleanPoints
FROM Player_Detail
);
GO
--Table 1
SELECT *,
( SELECT MAX(totalPoints)
FROM
( SELECT
RANK() OVER (ORDER BY SUM(cleanPoints) DESC) AS Position
, Player
, CAST(SUM(cleanPoints)AS DECIMAL(5,2)) AS totalPoints
FROM
( SELECT Player
, cleanPoints
, RANK() OVER (PARTITION BY Player ORDER BY cleanPoints DESC) AS rank1
FROM dbo.Clean_Player_Detail
) t1
WHERE rank1<=10
GROUP BY Player ) t2 ) - totalPoints AS pointsBehindLeader
FROM ( SELECT
RANK() OVER (ORDER BY SUM(cleanPoints) DESC) AS Position
, Player
, CAST(SUM(cleanPoints)AS DECIMAL(5,2)) AS totalPoints
FROM
( SELECT Player
, cleanPoints
, RANK() OVER (PARTITION BY Player ORDER BY cleanPoints DESC) AS rank1
FROM dbo.Clean_Player_Detail
) t1
WHERE rank1<=10
GROUP BY Player ) t2;
--Table 2
SELECT
RANK() OVER (ORDER BY SUM(cleanPoints) DESC) AS Position
, Player
, COUNT(cleanPoints) AS numEvents
, CAST(SUM(cleanPoints)AS DECIMAL(5,2)) AS totalPoints
, CAST(AVG(cleanPoints)AS DECIMAL(5,2)) AS avgPoints
FROM
( SELECT Player
, cleanPoints
, RANK() OVER (PARTITION BY Player ORDER BY cleanPoints DESC) AS rank1
FROM dbo.Clean_Player_Detail
) t1
WHERE rank1<=10
GROUP BY Player;
HW1 workflow.yxmd (24.5 KB)
That took me a bit. Learned a few new tools. I’ve included the workflow, but not the pdfs as they are a supported file type to upload. Thanks!
This took me longer than it probably should have- but I am learning Alteryx! I learned how to use Muli-Rows, as well as the limits of Multi-rows. I had hoped to be able to have them refer to a static value and then just calculate items such as the Points from the Leader in one step, but turns out you have to go around that ordering. Also- it wasn’t clear in the second one that we also assume the top 10 events are what we total by but NOT what we average by so I struggled to figure that one out, Other than that it was a great practice of how to make a more advanced data flow!
Same here! It was tough but glad to see you were able to figure it out! One thing I would suggest though is to make it so your Formula to find points behind the leader a non-static (dynamic) value so it would adjust to any point value that is listed in the top column (not just this data set)
This was a super fun challenge to do! Upon looking at other’s answers, I think I found a much simpler way to find the solution that involves a more condensed workflow. I loved learning about the PDF tool and how to make the table easier to read. A cool tip, is you don’t actually need to round the data prior to using the table feature. In the table, you can select the name of the column and specify how many decimals you want! AlteryxWorflowChallenge5.yxmd (21.7 KB)
nice work
