2019: Week 18 Solution
You can find our full solution workflow below and download it here!
Learning Objectives:
• Explore some other filtering methods.
• Practice using splits and pivots.
• Learn how do to multiple aggregation types on the same field.
- Range
- Minimum
- Maximum
This allows us to use a slider or text boxes to define the filter condition opposed to having to manually write a filter calculation. In the case of this challenge, selecting Minimum and entering in 10000 will filter out the low viewership shows as required.
1. Use a custom split on every ‘,’ to create 8 new [genre – Split X] fields. Then remove the original [genre] field.
Split 1 should have zero null, whereas split 8 should be mostly nulls as not many shows had 8 genres.
2. Use a Columns to Rows wildcard pivot with a wildcard of “Split”.
This creates two new fields:
- [Genre Split] : Contains all the actual genres
- [Pivot1 Names] : The names of the original fields.
3. Remove the [Pivot1 Names] field and filter out all the nulls in the [Genre Split].
[Pivot1 Names] isn’t useful to us and because many of the [genre – Split X] fields were full of nulls our pivot out is as well. From here, we are now ready to aggregate!
1. Before the aggregation, duplicate the field you want to perform multiple aggregations on by creating a calculated field which simply contained the original field.
In our case we could create a new field called [Rating 2] which just contains [Rating]. Then in the aggregation we can take the AVG of [Rating] and the MAX of [Rating 2]. The other method is:
2. Use two different aggregation steps, one for each type of aggregation, and then just join the results back together.
After our pivot we can create one aggregation step which finds the AVG of [Rating] and also create a second aggregation step which finds the MAX of [Rating]. As they use the same groupings ([Type] & [Genre]) we can simply join the results of these two aggregation steps together on the grouping fields.
 |
| Our full solution workflow. |
Learning Objectives:
• Explore some other filtering methods.
• Practice using splits and pivots.
• Learn how do to multiple aggregation types on the same field.
Filtering out shows with less than 10000 viewers
Here is a filtering method you may now have come across yet: On the [Members] field, click the 3 dots, then click Filter, and then select Range of Values. This gives us 3 options:- Range
- Minimum
- Maximum
This allows us to use a slider or text boxes to define the filter condition opposed to having to manually write a filter calculation. In the case of this challenge, selecting Minimum and entering in 10000 will filter out the low viewership shows as required.
Splitting & Pivoting the genres so they can be aggregated
In order to calculate the viewerships and rating for each genre we need to transition from having multiple genres in a single row to having a row for every genre in every show.1. Use a custom split on every ‘,’ to create 8 new [genre – Split X] fields. Then remove the original [genre] field.
Split 1 should have zero null, whereas split 8 should be mostly nulls as not many shows had 8 genres.
2. Use a Columns to Rows wildcard pivot with a wildcard of “Split”.
This creates two new fields:
- [Genre Split] : Contains all the actual genres
- [Pivot1 Names] : The names of the original fields.
3. Remove the [Pivot1 Names] field and filter out all the nulls in the [Genre Split].
[Pivot1 Names] isn’t useful to us and because many of the [genre – Split X] fields were full of nulls our pivot out is as well. From here, we are now ready to aggregate!
Getting all the aggregations
In our requirements we not only want the average rating for each genre and show type combination but also the max rating. However, when using an Aggregation step you can’t use the same field more than once. There are two methods to side-step this:1. Before the aggregation, duplicate the field you want to perform multiple aggregations on by creating a calculated field which simply contained the original field.
In our case we could create a new field called [Rating 2] which just contains [Rating]. Then in the aggregation we can take the AVG of [Rating] and the MAX of [Rating 2]. The other method is:
2. Use two different aggregation steps, one for each type of aggregation, and then just join the results back together.
After our pivot we can create one aggregation step which finds the AVG of [Rating] and also create a second aggregation step which finds the MAX of [Rating]. As they use the same groupings ([Type] & [Genre]) we can simply join the results of these two aggregation steps together on the grouping fields.
