92|ALTERYX – Burned Out Beach Bum

[Brett_Lowe]

BYU Student Author: @Brett_Lowe
Reviewers: @Mike_Paulsin, @Alex_Garrett
Estimated Time to Solve: 70 Minutes

We provide the solution to this challenge using:

• Alteryx

Need a program? Click here.

Overview
Brittany is on the tail-end of busy season and is believably burned out. This morning, she woke up to a tap on her shoulder and was both bewildered and bothered to find her boss blinking back at her. “Bertha, what are you doing in my house?” she breathed, groggily. Bertha bluntly brought up that they were actually in the office. “Brittany, you reek of anxiety, chow mein, and burnout. You’ve been working yourself too hard, so I’m sending you on a paid vacation before you go berserk.” Blown away, Brittany agreed and thanked Bertha.

Because Bertha is a good boss, she will pay for the vacation and allow Brittany to select the destination. Based on her preferences below, help Brittany find the best option!

Instructions
Bertha is rich in resources but isn’t very resourceful. The data she provided Brittany is a mess. In her burned-out state, Brittany isn’t bright enough to google the information herself, but she’s brilliant in Alteryx, especially Regex, and decides to deal with what she’s been given. Brittany can sense the beach beckoning to her and wants to be close to an ocean. She’s also aiming for somewhere warm, and because Bertha is paying, Brittany wants to break Bertha’s Bank. Your goal is to find the most expensive destination with an average temperature warmer than 75 degrees Fahrenheit and a beach that is closer than 5 miles away.

1. Input, parse, and clean the data.
   • From the Destination column on the first sheet, create two new columns: DestID and DestName
   • From the Distance column on the second sheet, create the following new columns: DestID, DestName, ClosestBeach, Dist1, Unit1, Dist2, Unit2
   • From the Coordinates column on the third sheet, create the following new columns: DestID, Lat, Long
   • From the Temperature column on the fourth sheet, create two new columns: DestName, TempC
   • Using the data on the fifth sheet, create a new column called TotalCost that shows the total cost of the trip.
   • Join all the data.
2. Create a new column called MilesToBeach that shows how close the destination is to the beach, in miles.
3. Create a new column called TempF that converts the average temperatures from Celsius to Fahrenheit. Remember that (Celsius *9/5) + 32 is the correct conversion.
4. Filter out destinations that are 5 miles or further away from a beach and 75 degrees Fahrenheit or cooler.
5. After filtering based on Brittany’s preferences, select the destination that costs the most.

Data Files

• Challenge92_Data.xlsx
• Challenge92_Data_Dictionary.docx

Suggestions and Hints

• If the RegEx is giving you trouble, consider looking through some of the preset expressions. There are several ways to write the syntax but using the “^” symbol may prove useful.
• If you end up with a tie, consider using the coordinates to make sure each destination is actually near an ocean.

Solution

Solution Image

Challenge92_Solution.yxmd
Solution Video: Challenge 92|ALTERYX – Burned Out Beach Bum

[Meg_McEachern]

This challenge was super hard but I tried my best! I followed the video to the best of my ability but I think my regex was still a little messed up. Super interesting though!

[Caleb_McKay]

Very difficult challenge! One problem I had was that some beaches had the same town as their DestName so that does not make for a good primary key to join on. I had 122 records out of my last join but luckily the other filters took the extra records out. I also did not end up with a tie because in my records, India was over 1000 miles away from the beach.

[Christina_Goodin]

I don’t think I did it as efficiently as I could have but it was a good challenge!

[Julianne_Goodin]

Here is my solution!! This was a hard challenge.

[meganebarton]

This was an incredibly difficult challenge for me! I definitely had to result to the help of the video! I loved the regex practice and was able to get pretty close to parsing the second sheet on my own after debugging it for a bit! Definitely took me two hours to get through this one haha

[Evan_Scott]

ALTERYX92.yxmd (32.8 KB)

RegEx for sheet2: (\d*).\s([a-záéíóúñüÁÉÍÓÚÑÜ|\d|,|.|\s|/])-\s([a-záéíóúñüÁÉÍÓÚÑÜ|\d|.|\s|/]),+\s([\d|.,])\s([a-z|.])\s(([\d|.|,])\s*([a-z|.])).*

[Evan_Scott]

Join multiple is a good one for this project because several tables have the same foreign key. I’m no expert on Join Multiple though, perhaps there’s a better way.

[Dallin_Hwang]

This one was pretty fun. The regex was giving me a hard time for a little bit, but I learned some new stuff about regex in the process, so I’d say it was worth it.

[linnea]

92.yxmd (28.3 KB)

[Matt_Bailey]

92-burnedout.yxmd (36.0 KB)

Wow, this challenge really pushed me on my regex skills, definitely the part that took the longest. Thanks for the practice, I felt like it pushed me to review regex!

[luisg99]

Really good challenge to practice Regex Skills, that was the most difficult part for me about the challenge and it was my first time using the sampling tool! Overall a great challenge, good difficulty
challenge92alt.yxmd (33.7 KB)