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

[jdunkerley79 · Accepted solution]

I finished my approach off. Takes about 1 minute to run on mine.

It produces every possible path. I think there are 10,524 in the data set you gave.

The first macro as before collapses cases down (this takes 161 iterations).

Next, it walks back from the trip with no destination.

This second iterative macro works out each possible join.

Finally it builds out all the paths in full (10mm rows) as well as picking the first as well.

[alexey_nikityuk]

@jdunkerley79 this is so cool! Thank you so much for that. I see that it produces 10K scenarios, what Im gonna do now is to select the one where the total direct distance between the points will be the smallest. 

Again, hard assumption, but without additional inputs I would just use common sense I guess. 

Thanks everyone again! You guys are great!