2022 Week 8 - Solution
Solution by Tom Prowse and you can download the workflow here.
In this week's Preppin' Data we're collaborating with #GamesNightViz on their latest challenge Power Ups. We'll be diving into the Pokédex to explore how Pokémon combat stats change when they evolve.
Step 1 - Pivot Pkmn Stats
The first task is to input the pkmn_stats data set where we can clean this up a little by removing the following fields:
- Height
- Weight
- Evolves From
- hp
- defense
- attack
- special_attack
- special_defense
- speed
Step 2 - Join Evolutions Data
Next we want to input the pkmn_evolutions table, and use this to look up the combat factors for each Pokemon at each stage. To get the correct values for each stage, we need to do a join for each stage (3 in total) where we join the name and the stage number.
The first join looks like this, where name = Stage_1
After the join we can rename the Values field to 'stage_1_values' and the table should look like this:
Next, we repeat this same process but this time join on name = stage 2. We are joining from our latest step (where we renamed stage 1 values) back to the pivot step that we used earlier.
As well as joining on name = stage 2, we also need to include combat_factors = combat_factors to ensure we are matching the correct values. The join should look like this:
Again we can rename the values field our table looks like this:
The final join that we need to do is based on stage 3. We are again joining back to the pivot step, where name = stage 3 and combat factors = combat factors, however this time we need to include all values from our current branch (this will be a left or right join depending on the order that you completed the join).
The join conditions should look like this:
And then finally we can rename the fields again so that our table now looks like this:
Step 3 - Final Combat Power
The next step is to find the combat power of the Pokemon when it is in it's last evolution stage. To do this we can use an IF statement to identify whether the Stage 3 is Null, and then return either the Stage 2 or 3 value.
After this calculation we no longer need the stage 2 values & stage 3 values fields, so we can remove these and our table should now look like this:
We can then use an aggregation field to sum the total combat power for each Pokemon. In the aggregation we need to group by Stage 1, Stage 2, Stage 3, Pokedex Number, & Gen Introduced, and then sum Final Combat Power and Initial Combat Power:
As a result of the aggregation our table should now look like this:
Step 4 - % Increase
This provides us with the increase or decrease in a decimal form.
Then finally we want to sort these based on that calculation. To do this we can use a Rank calculation where we rank the combat power increase field in an ascending order:
Rank
After we have completed the ranking, then we are ready to output our table in the desired format.
