Hi - hoping someone may be able to help please. This is way beyond my level of knowledge:
I have one set of data from a meeting room booking system (booked data) in one tab and another set of data from sensors in the meeting room (actual data) in another tab. I want to compare / match various elements of the 2 sets of data to determine if the booked meeting actually took place.
Sample of booked data:
Booking Date Room Event Start Event End
2/3/18 Room 10.1 8:30 am 9:30 am
Sample of actual data:
Time Room In Use
2018-03-02 08:00:00 +1000 AEST Room 10.1 0
Other things to know:
- There will be multiple rooms so I also need to match the room names across each data set.
- The booked data will only ever have one row for a meeting for that given room and start / end time
- The actual data can have many rows of data that has a time that falls within between the Event Start and Event end times from the booked data. This is because the sensors are sending data back every 2 minutes for each seat in the room. For example, if we had 4 seats in Room 10.1, each with a sensor and a 60min meeting, we would have 120 lines of data that may have a time stamp that falls between the meeting start / end time from the booked data.
- The "In Use" element on the actual data is either 0 (seat not in use) or 1 (seat in use). If we have a 1 on any of the lines of actual data that fall within the booked window, then by definition the meeting took place because at least 1 seat was occupied.
- The date / time formats shown above is as it is in Excel
So - in my dumb speak the logic would be something like:
Where actual room name = booked room name and;
actual date = booked date and;
actual time is => event start and =< event end and;
actual in use is = 1 then;
mark row of booking data with "meeting happened" else;
mark row of booking data with "meeting did not happen"
Hope this makes sense and thanks in advance for any help.
I have one set of data from a meeting room booking system (booked data) in one tab and another set of data from sensors in the meeting room (actual data) in another tab. I want to compare / match various elements of the 2 sets of data to determine if the booked meeting actually took place.
Sample of booked data:
Booking Date Room Event Start Event End
2/3/18 Room 10.1 8:30 am 9:30 am
Sample of actual data:
Time Room In Use
2018-03-02 08:00:00 +1000 AEST Room 10.1 0
Other things to know:
- There will be multiple rooms so I also need to match the room names across each data set.
- The booked data will only ever have one row for a meeting for that given room and start / end time
- The actual data can have many rows of data that has a time that falls within between the Event Start and Event end times from the booked data. This is because the sensors are sending data back every 2 minutes for each seat in the room. For example, if we had 4 seats in Room 10.1, each with a sensor and a 60min meeting, we would have 120 lines of data that may have a time stamp that falls between the meeting start / end time from the booked data.
- The "In Use" element on the actual data is either 0 (seat not in use) or 1 (seat in use). If we have a 1 on any of the lines of actual data that fall within the booked window, then by definition the meeting took place because at least 1 seat was occupied.
- The date / time formats shown above is as it is in Excel
So - in my dumb speak the logic would be something like:
Where actual room name = booked room name and;
actual date = booked date and;
actual time is => event start and =< event end and;
actual in use is = 1 then;
mark row of booking data with "meeting happened" else;
mark row of booking data with "meeting did not happen"
Hope this makes sense and thanks in advance for any help.
