This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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!