Preppin Data Week 14

The #Preppin Data week 14 challenge asked us to determine the impact of implementing a meal deal at Whyte’s Cafe. I thought this was a good challenge and per usual I broke the steps out into a number of steps. Seeing the solutions for the weekly challenges made me realize that I usually have a different approach than the solution and that I like to break things out into multiple steps and this week was no different.

Here is what my final flow looks like:

PreppinWeek14

I connected to the .csv file and inserted a clean step. The steps say to replace null prices to 1.5, however, I didn’t have any nulls when I imported my data so I skipped that step. They also stated to replace the null member ids with a zero. This is the only action in my first step. To do this I right clicked on the null in the member id field and selected edit value and replaced the null with 0.

The output needed to have the total ticket price, the number of meal deals, the total prices for items not included in the meal deal, and the difference between the total price and the meal deal adjusted price. I determined these through 3 different branches.

The first aggregate I created was to determine the number of meal deals per ticket. To get this I needed to know the number of items per ticket.

Week14ItemAgg

After the aggregate I added a pivot step to move the type from rows to columns. I did this so I could use the columns to determine the number of meal deals per ticket.

Week14ItemPivot

I then inserted a clean step to create the meal deal flag and the number of meal deals per ticket. This is my approach:

  • determine if the ticket has items that are meal deal eligible
    • [Drink] > 0 AND  [Snack] > 0 AND  [Main] > 0
  • right clicked on the new meal deal eligible field and kept true
  • determine number of meal deals per ticket:
    • IF [Drink] <= [Snack] AND [Drink] <= [Main] THEN [Drink] ELSEIF [Snack] <= [Drink] AND [Snack] <= [Main] THEN [Snack] ELSEIF [Main] <= [Drink] AND [Main] <= [Snack] THEN [Main] END

The next step was to get the average cost per item and ticket. I created this to use to determine the excess cost later in the flow. Because drinks, mains, and snacks all have different prices I used the average by type to get the excess cost.

Week14ItemAvgCost

I also pivoted the types to columns and then added a clean step. In the clean step I replaced any null values to a zero.

The third aggregate totaled the cost per ticket.

Week14TicketTotal

I added a clean step after the aggregate to round the total cost to 2 decimals. I used this calculation ROUND([Total Ticket Price],2).

After the aggregates were ready to go I joined the 3 branches together and started the final steps. These are the calculations I did in the costs step:

  • determines meal deal cost:
    • Meal Deal Total: [Min Meal Deal Item] *5
  • determine number of items that aren’t part of the meal deal (3 different calculations):
    • [Drink_Items] – [Min Meal Deal Item]
    • [Snack_Items] – [Min Meal Deal Item]
    • [Main_Items] – [Min Meal Deal Item]
  • determine cost of excess items:
    • [Excess Drink Items] * [Drink_Price]
    • [Excess Snack Items] * [Snack_Price]
    • [Excess Main Items] * [Main_Price]
  • determine excess total:
    • ROUND([Excess Drink Cost] + [Excess Main Cost] + [Excess Snack Cost],2)

Because I like to break things up I inserted another clean step. These are the calculations I used to get the final output.

  • determine the new total price using the meal deal:
    • [Meal Deal Total] + [Excess Total]
  • determine the cost difference:
    • [Total Ticket Price] – [Total Ticket Meal Deal Adj]

My meal deal items and costs matched the solution output but my prices do not and I haven’t been able to figure that out. I have done pivots in excel to get the cost by ticket and they match what I have in my output. I decided to not get too hung up on that an use this week as a concept and not exact match.

I enjoyed this week’s challenge and I continue to enjoy seeing the different approaches to the challenge each week.