2022: Week 37 - Solution
Solution by Tom Prowse and you can download the workflow here.
The challenge this week is another collaboration with #GamesNightViz and this time we are looking to find the highest damaging weapons used in the game Elden Ring.
Step 1 - Damage Stats
- Name
- Phy
- Mag
- Fire
- Ligh
- Holy
- Str
- Dex
- Int
- Fai
- Arc
- Category
Then from the input step we can create a columns to rows pivot with Phy, Mag, Ligh, Holy, and Fire as the columns
From here we can then split apart the Pivot Values field so that we have the Attack Damage and the Damage Resistance. We need to split the field using a custom split on ' ' (space) so that we have two separate columns.
Our table should now look like this:
Next to tidy our table we need to remove dashes (-) from the Attack Damage field. To do this we can change the data type to a Whole Number and then use the ZN() function to bring all of the - to a 0.
Finally we want to aggregate to find the total attack damage by using an aggregate step where we group by Category and Name, then Sum Attack Damage
At this stage we have finished the damage stats branch and our table should look like this:
Step 2 - Level Requirements
Next we need to return to the input step and then create a separate branch for the level requirements stats.
This time we are going to repeat the process but bring Dex, Arc, Fai, Int, and Str into our columns to rows pivot:
Then we want to split the Pivot Values field using a ' ' and rename both fields to Required Level and Attribute Scaling.
After the split, we can then make sure to replace any '-' with a 0, by changing required level to a whole number and using the ZN() function to create the 0's.
Finally, we need to repeat the aggregation step but this time we want to group by Name and Sum Required Level:
After the aggregation our 2nd branch should look like this:
Step 3 - Combine and Rank
The next stage is where we combine both of the branches using an inner join where Name = Name.
This creates a single table where we can see the Required Level and Attack Damage for each Category and Name:
Now we have a single table we can create some ranks to help compare each of the weapons.
First we want to rank the weapons for each Required Level:
Then we want to only retain the top weapon for each, therefore we want filter the Weapon Rank field to only retain values that are ranked 1. Weapon Rank = 1.
After removing the Weapon Rank field we are now ready to output our data:
Will has also provided the solution for the bonus challenge which can be downloaded here.
