Forum Discussion
Helper IFind corresponding worker based on shift begining and end datetimes
I have table A of events:
| Event ID | Station ID | Timestamp (datatime) |
| 10 | A | |
| 20 | B | |
| 30 | A |
And I have table B of workers and their shifts:
| Station ID | Worker ID | Shift start (datetime) | Shift end (datetime) |
| A | 1 | ||
| A | 2 | ||
| B | 1 | ||
| A | 1 |
I would like to fill a column on table A with the corresponding Worker ID, so I can calculate things like number of events per worker, etc.
Note datetime granularity is needed. Shifts may or may not start one day and end on the next one. There is no overlap of shifts. Some intervals of time may have no worker assigned. There is no more than one worker assigned at any given time-station combination. The same worker may be assigned to different stations on different datetimes.
How would one go about modeling this relationship in an elegant manner?
I would keep the tables disconnected, or - if you must - create a Stations dimensional table.
Regardless, the calculated column in the Events table would just reference the entire workers_shifts table with the appropriate filters
Meta Code:
Emp =
var s = selectedvalue([Station ID])var d = selectedvalue([Timestamp])
Return calculate(max(workers[Worker ID]),workers[Station ID]=s,workers[Shift start]<=d,workers[Shift end]>=d)
7 Replies
- lbendlin
Super User
You may want to provide more details. What if worker shifts overlap and the event at the station happened during that time?
igonzalezblbendlin Hello. As stated in the post, there is no overlap. Let's assume there are no edge cases.
- lbendlin
I would keep the tables disconnected, or - if you must - create a Stations dimensional table.
Regardless, the calculated column in the Events table would just reference the entire workers_shifts table with the appropriate filters
Meta Code:
Emp =
var s = selectedvalue([Station ID])var d = selectedvalue([Timestamp])
Return calculate(max(workers[Worker ID]),workers[Station ID]=s,workers[Shift start]<=d,workers[Shift end]>=d)
- igonzalezb
That works! Had to make some modifications though. Code ended up being this:
Assigned Worker = CALCULATE( MAX(workers[Worker ID]), workers[Station ID] = EARLIER([Station ID]), workers[Shift start]<=EARLIER([Timestamp]), workers[Shift end] >= EARLIER([Timestamp]) )Thank you, lbendlin