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

[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!

[jdunkerley79]

I'd start by simplifying the problem set.

You can take the unique steps and merge them into a single multi-step path. This would hopefully work to make the iterative part you have simpler.

Could you mask the data set and post a full scale set as I think the difficult part on this one is producing a scalable solution?

[alexey_nikityuk]

Hi @jdunkerley79

Thank you very much for your reply. I attach the full dataset as it is + my ideas on how the process should work in the "manual" mode. I feel Im onto something with it, but not sure how to create the iteration as with Append tool field names keep changing.

Can you please help?

[alexey_nikityuk]

Hello @CharlieS

Wanted to add you to this conversation as you built the initial iterative macro I was using as reference. Just in case you may have some ideas on how to scale it when the number of rows is greater than 10....

Thanks a lot in advance!

[clant]

Hello Alexey,

The problem you have in your data is you have multiple flights to and from the same airport. So when you try to find the next flight there are multiple flights it could be. For example if you look at AL Bad` this could be fliying to either An Nazim or 'Ajmiya, there is no way to know which one its going to.

The finding the distances for each flight is relatively simple. You just need to do a join on your data to bring in the destination airport Lat/long. You then use the create point tool twice to create a point from both Lat long and use the find distance tool to get the distance.

Hope this helps, below is the full list of flights from the same location

RecordID CityFrom CityTo LEG
37 Ad Dar al Bayda' Judaira
210 Ad Dar al Bayda' Bulaida
198 Al Bad` An Nazim
934 Al Bad` `Ajmiya
367 Al Ghayl Diri
910 Al Ghayl Naghbi
414 Al Hasan Ash Shuqayri
786 Al Hasan Al Mahawiyah
20 Al Khadra' Tuwayriq
155 Al Khadra' Samitah
633 Al Qa` Ahl Muhammad
711 Al Qa` Qasr al Ballaji
468 Al Qawsah Umm Ama'in
947 Al Qawsah Sarra'
191 Al `Awali Al `Ammaj
996 Al `Awali Al `Ababid
122 Al `Aziziyah As Sulaimi
584 Al `Aziziyah Rafia Uwadah
189 An Nasiriyah Al Muwaylih
240 An Nasiriyah An Namudhajiyah
652 Ar Rawd Al `Arfajiyah
878 Ar Rawd Jarudiyah
522 Ar Ruwaydah Rijm al Shuyukh
541 Ar Ruwaydah Badr
366 Ash Sha`ar Al Quful
664 Ash Sha`ar Al Haras
46 Ash Shumaysi Rawdat al Faras
520 Ash Shumaysi Al Hijun
90 Badr Al Nuqaia`h
156 Badr Ad Duhu
661 Dalqan Ash Sha`ra'
997 Dalqan Khaif Fadhil
51 Sha`bah Kuluf el Masri
449 Sha`bah Abu Sulal

[alexey_nikityuk]

Hello @clant

Thanks a lot for your message. Indeed, I was aware that there are cities where the person was going multiple times, but this is exactly the complexity that I am trying to solve.

I was thinking to calculate the distance between the first "repeat" point to all of its possible directions and pick the shortest one, which I agree is a questionable logic but still. 

At the same time, we know the first point and the last point of the itinerary. So i think its also an optimization game when Alteryx logic should calculate all possible destianations for those "repeat" lcoation and find the ones the itinerary that will bring the person from the intial point to the last one. Was thinking if I can do Append field XX-ish times so it will create absolutely all possible combinations of those location and then try to filter out lines where the start equals the end point. 

Do you think it can be possible? 

@jdunkerley79 @MarqueeCrew - what do you guys think?

[danilang]

Just throwing this out there since it was never mentioned in the thread.  Assuming this a single trip, is it possible to get time information from the travel log?  Adding this makes it a simple linear path and an easily solved problem

Dan