Travel itinerary sequence

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/m-p/137544/highlight/false#M37127 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

Travel Sequence.xlsx

Iterative Macro

Accepted answers

CharlieS

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.

20181105-Iterative with Duplicates.yxzp

jdunkerley79

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.

Travel Sequence JD.yxzp

All comments

MarqueeCrew

This sounds like a test question for certification. Have you built an iterative macro to try solving?

alexey_nikityuk

Hello! Im not sure if its a certification question tbh, my friend asked me for help around his start-up...

On iterative macro, as mentioned, I tried to use the one from the link I shared. But it works only when there are 10-20 lines. When there are 1000 lines it just keeps spinning. I left it overnight and the macro made only 35 iterations with 65mil lines (it uses append formula)... clearly something is wrong and I am sure there is a better way...

Any help is greatly appreciated!

alexey_nikityuk

Guys, really need a community help this time as a task happened to be more complex as I thought. So would really appreciate joint ideas around potential solution.

Thanks a lot in advance!

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335