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

Next, we want to pivot this data so that each of the 'combat factors' are in a single field. To do this we can use a Columns to Rows pivot, where we pivot the following fields: 

• hp
• defense
• attack
• special_attack
• special_defense
• speed

We need to drag each of these fields into the 'Pivoted Fields' section of the pivot: 

After the pivot our table should now look like this:

Note we have renamed the pivot names and values to combat_factors and values.

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. 

Final Combat Power 

IF ISNULL([stage_3_Values])

THEN [stage_2_Values]

ELSE [stage_3_Values]

END

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

The final step this week is to calculate the % increase (or decrease) between the initial and final combat power for each Pokemon - therefore allowing us to determine which Pokemon stats decrease from evolving.

First we need to calculate the combat power increase with the following calculation: 

Combat Power Increase 

([final_combat_power]-[initial_combat_power])

/

[initial_combat_power]

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.

You can download the full output here. 

After you finish the challenge make sure to fill in the participation tracker, then share your solution on Twitter using #PreppinData and #GamesNightViz and tagging @Datajedininja, @JennyMartinDS14, @TomProwse1 & @WJSutton12

You can also post your solution on the Tableau Forum where we have a Preppin' Data community page. Post your solutions and ask questions if you need any help!