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

How to rank my data by a certain rule

meganhan
7 - Meteor

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,

 

8 REPLIES 8
MichalM
Alteryx
Alteryx
fmvizcaino
17 - Castor
17 - Castor

Hi @meganhan ,

 

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

 

Best,

Fernando V.

meganhan
7 - Meteor

Hi,

 

This is the full data.

 

Thank you very much!

OllieClarke
15 - Aurora
15 - Aurora

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

 

OllieClarke_0-1573731285268.png

 

OllieClarke
15 - Aurora
15 - Aurora

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

OllieClarke_0-1573732540987.png

danilang
19 - Altair
19 - Altair

Hi @meganhan 

 

Here's another solution similar to @OllieClarke's

 

main.png

 

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

 

macro.png

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.

 

r.png

yunpeng
8 - Asteroid

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

 

yunpeng_0-1597770133316.png

 

OllieClarke
15 - Aurora
15 - Aurora

Hey @yunpeng,

 

I tweaked your setup to get it working as below

OllieClarke_0-1597847466022.png

 

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

Labels