Hi, I am trying to identify how to add values if they are within a certain range, i have tried col sum but I do not know how to do compare the table values, (new to scripting )
the idea is that i have customers coming in at a certain time, stay for x minutes and leave, as the get in to the area, occupancy (number of customers) increase, if they are together within a certain time range.
I'd really appreciate guidance, thanks.
FC
Your inquiry is in line with a test that I have to build for our Model Development project. Your wording is a little confusing, but I surmise that what you effectively need to do is create an occupancy table based on the entry and exit events at a service location for each customer. The occupancy at any given time is complicated by the interdependence of each customer's exit time which may span several entry events. This is a typical simulation modeling problem when attempting to determine utilization, which is a level above what you are attempting.
Before showing the necessary script, I will walk you through the process.
First, I created a data table similar to your example. In this form the data is agent-centric with the entry and exit events recorded in the same row. We need to separate the events by stacking the events.
Now the events have been separated, but by default they are still grouped by the Agent. We need the events in order by time, so the data table needs to be sorted. Before sorting, we also need to make sure that exits occur before entries. The latter may not be important for your purpose, but we always deal with systems with limited capacity, so entry and exit events that have the same timestamp need to be ordered as exit then entry, as the it is most likely that an agent exits a location before a new agent enters. Once that is done we sort the data table by Time and Action.
Now that the events are in the proper order, a column can be added to track occupancy.
The Occupancy column has a formula that determines the occupancy in each row based on the previous occupancy and the action in the current row. The formula also assumes that the first row is the first entry—it should be—and sets the occupancy to 1 for the first row. Now we need to split the table so that we get the original table orientation back with the occupancy reported.
The Occupancy column now shows the occupancy based on each entry, but also accounts for previous exits from the service location. The split operation placed the Occupancy column after the Agent ID column, so the script will move it to the last column.
Here is the script:
Names Default to Here(1);
dt = Current Data Table();
dt << Stack(
Columns(:Entry, :Exit),
Source Label Column("Action"),
Stacked Data Column("Time")
);
Close(dt, NoSave);
dt2 = Current Data Table();
dt2:Action << Set Property("Value Ordering", {"Exit", "Entry"});
dt2 << Sort(
By(:Time, :Action),
Ascending,
Replace Table(1)
);
dt2 << New Column(
"Occupancy",
Formula(
If(Row() == 1,
1,
If(:Action == "Entry",
Lag(:Occupancy, 1) + 1,
Lag(:Occupancy, 1) - 1
)
)
)
);
dt2 << Split(
Split By(:Action),
Split(:Time),
Group(:Agent ID)
);
Close(dt2, NoSave);
dt3 = Current Data Table();
dt3 << Move Selected Columns({"Occupancy"}, To Last);
Thank you for the input.
I was wondering similar solutions for our occupancy which in this case is a Room Occupancy in Hotel Management.
I have the Check In Date and Check Out Date. When they check in it means they occupied a room and the number of rooms are limited to 516.
- How could I calculate row by row the Occupancy in % (number of rooms occupied/516).
- how could I calculate the number of rooms occupied for each given day
- Furthermore how could I calculate the occupancy by AgencyGroup
Many thanks in advance.
