Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.

Weekly Challenges

Solve the challenge, share your solution and summit the ranks of our Community!

Also available in | Français | Português | Español | 日本語
IDEAS WANTED

Want to get involved? We're always looking for ideas and content for Weekly Challenges.

SUBMIT YOUR IDEA

Challenge #6: Spatial Route

GeneR
Alteryx Alumni (Retired)

We hope you enjoyed last week's challenge.   For the sixth challenge lets look at some of the geospatial capabilities in Alteryx.

 

The challenge for week 6 will focus on analyzing distance traveled by sales reps.

Sales reps are travelling all over the US. The data contained in the workflow details the travel paths for 7 Reps to 7 different cities. The travel route is detailed as well. The objective of this challenge is to determine which Rep has logged the most miles. Please include the route traveled as a spatial object in the output.

 

We have listed this as an intermediate challenge since not everyone is familiar with the Spatial tools.  As always, we love to hear your comments. We hope you are having fun with the challenges!

JulieH
Alteryx Alumni (Retired)

The solution for this exercise has been uploaded, see the attachment above.  We'll be taking a break for the holidays, and will resume weekly exercises on the first Monday of January. Happy holidays to all!

TomWelgemoed
12 - Quasar

Hi,

 

Think there is an error in the output data that was submitted with the challenge. I get different results even if use the solution file provided, so it does look like the challenge file has incorrect output data.

 

Thanks,

Tom

 

P.S: For example, I get these values for Diana & Cathy in terms of distance travelled.

9077.844095
6777.003786

MarqueeCrew
20 - Arcturus
20 - Arcturus

@GeneR & @JulieH,

 

I was late to the party, but got different results (like @TomWelgemoed).  I've solved the problem in the same manner as Gene, so I'm in good company.  @LeahK, this could have been you posting here!

 

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
rajiv_tarafdar
6 - Meteoroid

Same here. Is this supposed to be a tricky question? 

KatieA
Alteryx Alumni (Retired)

Solution attached!

SeanAdams
17 - Castor
17 - Castor

Looks like I'm the only one who tackled this in a slightly ham-fisted way :-) - ah well, live and learn.

 

Spoiler
Split the data into 2 streams
Stream1:
- multi-row formula to carry down the "Previous city" to the next row
- Used the distance tool to figure out the distance for each leg
- Summarize tool to add this up, and summarize by rep

Stream 2: route poly

Then used a plain-old-join to bring them together.   

Same result as @TomWelgemoed, @MarqueeCrew, @rajiv_tarafdar, @KatieA 

Thank you @KatieA - your solution re-enforced for me that I need to spend as much (if not more) time on the formula capability of Alteryx, not just the pre-provided tools

 

Question for this group: Is there a way of digitally proving that the route in the solution matches the route we each provided?   I tried using the SpatialProcess tool with an InverseIntersection selector, but I'm not sure that this is the right way of approaching this.   Is this perhaps just as simple as doing a text-compare on the centroid cast to a string value?

smbourke
6 - Meteoroid

Hi Sean,

Late to the party here as I am going back and doing some of the old challenges.

In relation to your Question for the group: Is there a way of digitally proving that the route in the solution matches the route we each provided?   I tried using the SpatialProcess tool with an InverseIntersection selector, but I'm not sure that this is the right way of approaching this.   Is this perhaps just as simple as doing a text-compare on the centroid cast to a string value?

 

I used a Spatial Match tool, where the Target contains the Universe. This works as if the geometries are identical they will contain each other. Whereas if there is a difference they will come out of the U(nmatched) port.

See attached solution for an example, I have removed all 2nd destinations to create a different line. I have however done the text compare on the spatial geometry in the past (use a select tool and force the spatial object to a String).

SeanAdams
17 - Castor
17 - Castor

Thank you @smbourke - worked like a charm!

Appreciate the assistance.

NicoleJohnson
ACE Emeritus
ACE Emeritus

Apparently I was in the "ham-fisted" solution boat with @SeanAdams. :) But I did check the posted solution afterwards and marveled at the simplicity of it, so I'll be logging that one away for future use!

 

Spoiler
Two streams:
1. One with Polyline
2. Multi-Row Formula to determine "next stop" > Join by TripOrder = Next Stop to bring in the Spatial Object for the second stop for each record > Find Distance between First Stop & Next Stop > Summarize by REP > Join with Polyline.

WeeklyChallenge6.JPG