Travel itinerary sequence

[alexey_nikityuk]

Good Sunday morning,

I need community help arond the following task. I have the unsorted travel log with information of City From and City To + Long/Lat of City from. 

I know where I started, but then need to put this list in sequence, and calculate the direct distance between cities on each of the routes and sum up the total. 

Complications:

- I have list of 1000 lines

- There are few cities that are duplicated, meaning that traveler can return to the same city 2+ times

- Long/Lat of the same city is unique as travel visits not the very same places (i.e. San Francisco GOlden Gate bridge long/lat and then Union Square long/lat. Both will be considered as San Francisco but will have different coordinates). 

I found this solution here: https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Connect-the-travel-path-in-succession/... but it doesnt help when the number of lines exceeds 10 as the macro just keeps spinning and over the night for example the tool made only 35 iterations and now is processing 65M lines... I am sure there should be another way....

Attached is the simple example of the data set I am dealing with. Any help is much much appreciated

[alexey_nikityuk]

Thank you for an idea @danilang! at this stage we dont have date/time information, just the itinerary...

And again, I want to come back to this macro that @CharlieS shared earlier - it works perfectly on a sample of 5 - what I dont understand is why it stops working on population greater than dozen of lines.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Connect-the-travel-path-in-succession/...

[danilang]

Looking at the logic of the macro, it assumes a simply connected list.  As soon as I add a duplicate Arrival, i.e A->B, effectively turning the list into a tree, it starts looping and hits the iteration limit.   Your data has both duplicate departures and duplicate arrivals.

While you don't have time information, do you at least have the order of segments? 

Dan

[alexey_nikityuk]

@danilang, unfortunately not, the task is to create the order of the segments. 

[alexey_nikityuk]

@danilang @clant @jdunkerley79 @MarqueeCrew

Guys, can you please have a look at the attached solution. Clearly, there is a pattern there, but I dont know how to create an iteration. Is it something you can help with? 

[jdunkerley79]

The first step to simplify is attached.

This takes it from 1000 input to 35 by joining the first unique steps. Some odd tools in the middle to make the sequence.

I'll have a think on how to build a single journey from this. I agree with @danilang that I am not convinced no guarentee of a unique path

[alexey_nikityuk]

I do apologize @jdunkerley79, not sure I understand why we narrow the list of 1000 lines to those 35....Are those the cities where duplication is present? If so, I think it is to Summarize, Count Distinct, filter for those with Count 2+ and than Join with the remaining group, no?

At the same time, do you think there is a way to iterate the workflow I shared earlier? 

[CharlieS · Accepted solution]

Others have pointed out the issues here regarding calculating a route with multiple loops and variable lat/long assignments for some locations The brute force method exploring every potential route is likely the only way this will be solved (without additional data). 

Here's a quick and dirty modification of the macro I built in the other thread that takes a random path when multiple possibilities are available. This could solve it, but I don't know how many attempts it will take to link all 1000.

[jdunkerley79]

@alexey_nikityuk no the 35 paths are joining all the unique start and end together (i.e. if I got to Sevenoaks once then I know I can join the two trips).

you can then move to attempt to build a single path joining these together. Will put that part together tonight I hope

[alexey_nikityuk]

This is awesome @CharlieS! Thankyou so much!

It took 1700 iterations, and produced the ouput more or less needed. I will continue tweaking the logic but major part is done! Thanks again!

[CharlieS]

I'm glad I could help.

If you are able, I recommend spending more time investigating a rigorous methodology like @jdunkerley79 suggested.