community
cancel
Showing results for 
Search instead for 
Did you mean: 
Do you have the skills to make it to the top? Subscribe to our weekly challenges. Try your best to solve the problem, share your solution, and see how others tackled the same problem. We share our answer too.
Weekly Challenge
Do you have the skills to make it to the top? Subscribe to our weekly challenges. Try your best to solve the problem, share your solution, and see how others tackled the same problem. We share our answer too.
Unable to display your progress at this time. Please try again a little later, or contact an administrator if you continue to see this error.
Getting started with Designer? | Start your journey with our new Learning Path!

Challenge #101: The Search for Powder

Alteryx Partner

Can finally attach files, although cannot edit my previous reply

Alteryx
Alteryx

Cheated a little bit... Changed the URL to this one which only has the last 7 days of data

 

https://wcc.sc.egov.usda.gov/reportGenerator/view_csv/customMultiTimeSeriesGroupByStationReport/dail...

 

Just made it way quicker to download a much smaller data set that didn't need half the filtering.

 

 

Spoiler

RobertW_0-1572978628618.png

 

Alteryx Partner

A challenging and very interesting exercise - learned a lot, especially the value of caching data - yikes!

 

Spoiler
Process:
- Extract sensor IDs (first split columns on "(" then removed ")" - there are probably better ways)
- Add Record ID
- Count number of sensors
- Generate rows for each sensor
- Append with URL for data
- Replace "303" in URL with each sensor ID
- Download data for each sensor
- Split to rows (on "\n")
- Cache data - the total was approaching 1GB!
- Filter out data beginning with "#"
- Filter data to capture most recent 7 days (I used 2018-01-23 so I could compare my results to the "real answer")
  -- Something went wrong with the filters but I still got there - no time to debug as the overall solution still worked - and I should be working right now too!)
- Text to columns on ","
- Flag the most recent 7 days of data (since my filter failed somewhere)
- Multi row tool to add 7 days of snow accumulation
- Filter for 2018-01-23
- Sort by Snow accumulation (descending)
- Select first record
- Add points on Lat and Long
- Append with Ski Area data
- Calculate distance from sensor to Ski Area
- Sort by distance (ascending)
- Select first record
- Sensor 840 (Upper San Juan) had 1.4 inches of snow accumulation and was closest to Wolf Creek

Not quite the same (1.4 vs 1.3) as official answer, but I think I got the process reasonably there


MySolution.PNG

 

Asteroid
Spoiler
2019-11-24 17_28_53-Greenshot.png

DURANGO MOUNTAIN RESORT would be the place to be on 11/24/2019 if there was any snow happening this early in the season.