Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Travel itinerary sequence

alexey_nikityuk
8 - Asteroid

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

21 REPLIES 21
alexey_nikityuk
8 - Asteroid

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
19 - Altair
19 - Altair

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
8 - Asteroid

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

alexey_nikityuk
8 - Asteroid

@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? 

 

Capture.PNG

jdunkerley79
ACE Emeritus
ACE Emeritus

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
8 - Asteroid

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
17 - Castor
17 - Castor

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
ACE Emeritus
ACE Emeritus

@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
8 - Asteroid

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
17 - Castor
17 - Castor

I'm glad I could help.

 

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

Labels