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

Interesting Multi-Row Macro - Any help appreciated!

bhushana209
6 - Meteoroid

Hey all!

 

I'm currently using Alteryx to create a model to analyze bus routes in the U.S., and have an interesting idea I would like to implement, but am confused on how to do so. 

 

I've created a model that takes a list of possible bus trips (i.e. Washington D.C. to New York to Boston/Philadelphia to New York) and along with a score of how likely a passenger is to travel that trip on that route. My model predicts a certain number of passengers will travel that route over a period of time, and I have actual seat numbers for each route as well. 

 

AlteryxHelpRouteModel.PNG

 

 

For bus trips where predicted passengers are greater than capacity, I'd like to reallocate them to lower "scored" trips. For example: 

  • From the first row we now we have 400 passengers to reallocate who are traveling from Boston to Philadelphia
  • We could put all 300 of them on the trip right below because there are seats available, but I think that's unreasonable - I don't know how many passengers are on each segment of the trip (BOS-NYC and NYC-PHL)
  • Instead, I'd like to only put as much of their capacity as their share of score would suggests (i.e. 400 * .22= 88 passengers) and puts "388" into the reallocated passengers column.
  • There are remaining passengers that could be allocated (400-88 or 312), and the next row has additional passengers to spill (50), so now there are 362 potential passengers to spill then
  • This process repeats (i.e. you attempt to spill passengers to the next row) until the "route" ends in the data, and begins again on a new route (in this case DCAPHL)

 

I imagine this involves a combination of the running total and multi-row tools, but wanted to get some advice as an Alteryx beginner. My sample data is below and any advice you could provide would be appreciated!

 

Best,

-Shay

 

 

 

 

8 REPLIES 8
Aguisande
15 - Aurora
15 - Aurora

Hi,

Your approach, as far as I understood is good. But I believe that an iterative macro would do a better job on this.

The principle of allocating passengers, as you described, fits well with an iterative macro process, where the macro's left over passengers will be then used for the accomodations on the next route.

 

 Edit #1: I found this awesome article in the Community, that may clarify the process I mentioned above.

http://community.alteryx.com/t5/Alteryx-Knowledge-Base/CS-Macro-Dev-Iterative-Macros/ta-p/10303

 

bhushana209
6 - Meteoroid

Definitely agree that an iterative macro would be useful and have seen that post in the community, but would you have specific thoughts about how to implement this in an iterative macro?

 

No worries if not - I was just curious if you had any ideas!

jdunkerley79
ACE Emeritus
ACE Emeritus

See if the attached is close to what you need

 

Basically I keep a running total of excess people and allocate to the next bus in order grouped by start and end point.

 

 

jdunkerley79
ACE Emeritus
ACE Emeritus

Missed removing the seats which have been transferred.

 

Have attached a version which copes with this correctly.

 

Good fun challenge think this should work

bhushana209
6 - Meteoroid

Thanks so much for this - it's incredibly helpful!

 

Is there any way to incorporate the "share of overall preferability score" into the calculation, however? 

 

 

jdunkerley79
ACE Emeritus
ACE Emeritus

Try the attached.

bhushana209
6 - Meteoroid

Definitely didn't realize the multi-row formula was so powerful - thank you again!

jdunkerley79
ACE Emeritus
ACE Emeritus

You are most welcome. A fun challenge to learn on :)

Labels