Interesting Multi-Row Macro - Any help appreciated!

[bhushana209]

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. 

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

[Aguisande]

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]

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]

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]

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]

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 · Accepted solution]

Try the attached.

[bhushana209]

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

[jdunkerley79]

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