How to rank my data by a certain rule

[meganhan]

Hi all

I have some raw data like this:

Departure/Arrive

Portland Amtrak Station/Mount Pocono Deport

Indianapolis Downtown/Hohokus Bus Stop

Mount Hood Railroad/Cumberland Bus Depot

Mount Pocono Deport/Indianapolis Downtown

Hohokus Bus Stop/Mount Hood Railroad

And I want to change them like this:

Departure/Arrive

Portland Amtrak Station/Mount Pocono Deport

Mount Pocono Deport/Indianapolis Downtown

Indianapolis Downtown/Hohokus Bus Stop

Hohokus Bus Stop/Mount Hood Railroad

Mount Hood Railroad/Cumberland Bus Depot

How could I do that?

Thanks,

[MichalM]

@meganhan 

This should give you what you're looking for.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Iterative-Macro-to-sort-Route/td-p/145...

[fmvizcaino]

Hi @meganhan ,

Edit: I have noticed the logic now, I'm going to make an example for you 🙂

Best,

Fernando V.

[meganhan]

Hi,

This is the full data.

Thank you very much!

[OllieClarke · Accepted solution]

Here's a workflow and macro that gets you what you want @meganhan 

[OllieClarke · Accepted solution]

@meganhan Here's it with your data (I also increased the iteration limit of the macro and removed the unnecessary end bit of the workflow)

[danilang · Accepted solution]

Hi @meganhan 

Here's another solution similar to @OllieClarke's

The main workflow just finds the start record and sets the order on this record to 1 before passing all the records into the iterative NextStop macro

Inside the macro, the records are filtered so the one with the order number is sent to data output.  It's also joined to the remaining records to find the next stop.  The order on the found record is updated, unioned with the remaining records and sent to the iteration output to be processed in the next iteration of the macro.  On every iteration of the macro, the number of records decreases by one so the macro will end once all the records have be processed.  The macro also sorts the records by iteration, so there's no need to sort them in the main program.

[yunpeng]

@OllieClarke would it work if in the iterative macro, only the departure station is used and within each iteration a lookup(using find replace) is executed to find the arrival station for that departure (give there are no repetitive routes)? Then the result arrival station is used as the next iteration input. 

This is my original idea but iterative macro kept giving null result. Wondering which part is wrong. Would be great if we could have interactive lessons on macros too!

[OllieClarke]

Hey @yunpeng,

I tweaked your setup to get it working as below

I think the key thing is to find the arrival station in the departure station, and then append the next arrival station. You can then rename the original arrival station as the departure station, and the cycle continues.

I've attached the workflow with the macro too here. Hope that helps, Ollie