I am relatively new to JSL, but I have been authoring several scripts to handle processes for generating tables and graphs for testing that a software product that we are producing is properly reporting results. One of the issues I am having now is that I have to create a graph that shows reported times (easy) but also reflects the residuals from the reporting time bin into future time bins.
For example, I need to make a wait time graph where the x-axis runs from midnight to midnight with discrete 15-minute time bins. I have a data set that reports wait times and I bin the time that the wait begins. For simplicity, I single out one row where a wait time of 90 minutes is reported for an agent that enters a location at 9:05 AM. The time bin for that wait time is 9:00 AM the 90 minute wait time is reported and shown in the graph in the 9:00 AM time bin. In a traditional graph, all other times would be zero, as all other data was excluded, but in reality, the graph should show what that agent's wait time is across several time bins until the wait time is zero; effectively, what is the minimum amount of time the next person entering the location can expect to wait if they arrive at the location behind the agent waiting during any time bin where the current agent is still queued.
So I need to create a matrix of all times with values of zero in the time bins except for those listed below.
Time Bin Wait Time
9:00 AM 90 min
9:15 AM 80 min
9:30 AM 65 min
9:45 AM 50 min
10:00 AM 35 min
10:15 AM 20 min
10:30 AM 5 min
I already have a script that generates the columns for each time bin after the last column of the data table, and I have found that due to the number of conditions involved and that fact that where, when and how often nonzero wait times will occur in any given row of the matrix that using column formulas would be overcomplicated, if at all feasible. So I was hoping that there is some method for populating individual cells so that I use nested loops to go through each row of each column in the matrix and determine what wait time value should be placed therein.
My other option, which I would much prefer, would be to create a new data table that includes child records to handle the overflow data. For example, a subset of the data table is the following four rows:
Agent ID | Location | Day | Entry Time | Exit Time | Min Transit Time | Wait Time | Time Bin |
---|
184 | Egress Queue | 1 | 7:10:24 AM | 7:10:24 AM | 0 | 0 | 7:00 AM |
185 | FIS Entrance | 1 | 6:41:38 AM | 6:41:38 AM | 0 | 0 | 6:30 AM |
185 | Visitors Queue | 1 | 6:41:38 AM | 7:00:51 AM | 0 | 1153 | 6:30 AM |
185 | Baggage Claim | 1 | 7:01:59 AM | 7:01:59 AM | 0 | 0 | 7:00 AM |
The script would scan each row in the original table and copy it to the new data table. In the third row in the example above, the total wait time, 1153 s, is greater than the time spent waiting in the 6:30 AM time bin: 6:45 AM time bin - 6:41:38 AM queue entry time - 0 s queue transit time = 202 s of wait used in 6:30 AM time bin. So the new data table would add child rows resulting in the region of the data table shown above appearing as follows:
Agent ID | Location | Day | Entry Time | Exit Time | Min Transit Time | Wait Time | Time Bin |
---|
184 | Egress Queue | 1 | 7:10:24 AM | 7:10:24 AM | 0 | 0 | 7:00 AM |
185 | FIS Entrance | 1 | 6:41:38 AM | 6:41:38 AM | 0 | 0 | 6:30 AM |
185 | Visitors Queue | 1 | 6:41:38 AM | 7:00:51 AM | 0 | 1153 | 6:30 AM |
185 | Visitors Queue | 1 | 6:41:38 AM | 7:00:51 AM | 0 | 951 | 6:45 AM |
185 | Visitors Queue | 1 | 6:41:38 AM | 7:00:51 AM | 0 | 51 | 7:00 AM |
185 | Baggage Claim | 1 | 7:01:59 AM | 7:01:59 AM | 0 | 0 | 7:00 AM |
The rows in italics are the child rows for Agent 185 in the Visitors Queue showing the remaining wait times until Agent 185 has no more wait in the Visitors Queue. I could then use this table to generate the graph that I need because the child records would provide the additional data needed.
If anyone can provide a method for either of this solutions, it would be greatly appreciated.