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,

Help

Accepted answers

OllieClarke

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

iter workflow.yxzp

OllieClarke

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

iter workflow_1.yxzp

danilang

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.