Challenge #6: Spatial Route

[GeneR]

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]

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]

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]

@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

[rajiv_tarafdar]

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

[KatieA]

Solution attached!

[SeanAdams]

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]

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]

Thank you @smbourke - worked like a charm!

Appreciate the assistance.

[NicoleJohnson]

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.