I have been trying to figure this out but I'm at an impasse.
Say you have 100 rooms and you have varying hours of operation by day of week. I would like to count only those exams between the posted hours of operation per resource per day of week.
I have created a csv file with the information for the ROOM, DAY OF WEEK, BEGIN TIME, END TIME, MAX EXAMS
I have unqualified the ROOM and DAY OF WEEK so that the csv data will aline with the exam data but I am struggling with counting only those exams within the time range.
I'm not sure I understood what you explain, but I think you have to use the IntervalMatch function.
Extract form the help file :
IntervalMatch
The IntervalMatch prefix to a Load or Select statement is used for linking discrete numeric values to one or more numeric intervals. There is an advanced application of IntervalMatch involving an extended syntax, that you can read about here. The general syntax is:
matchfield is the field containing the discrete numeric values to be linked to intervals.
Loadstatement | selectstatement must result in a two-column table, where the first field contains the lower limit of each interval and the second field contains the upper limit of each interval. The intervals are always closed, i.e. the end points are included in the interval. A lower or upper limit denoted by a NULL value is considered to be - and respectively. Other non-numeric limits render the interval to be disregarded.
Before the IntervalMatch statement, the field containing the discrete data points (Time in the example below) must already have been read into QlikView. The IntervalMatch statement does not by itself read this field from the database table.
The intervals may be overlapping and the discrete values will be linked to all matching intervals.
Example:
Look at the two tables below! The first one gives the start and end times for the production of different orders. The second one lists a number of discrete events. By means of the IntervalMatch function we will logically connect the two tables in order to find out e.g. which orders were affected by disturbances and which orders were processed by which shifts.
OrderLog
Start
End
Order
01:00
03:35
A
02:30
07:58
B
03:04
10:27
C
07:23
11:43
D
EventLog
Time
Event
Comment
00:00
0
Start of shift 1
01:18
1
Line stop
02:23
2
Line restart 50%
04:15
3
Line speed 100%
08:00
4
Start of shift 2
11:43
5
End of production
First load the two tables as usual, then link the field Time to the time intervals defined by the fields Start and End:
Select * from OrderLog;
Select * from Eventlog;
IntervalMatch ( Time ) select Start, End from OrderLog;
The following table box could now be created in QlikView:
I'm attempting to count the EXAMs that match the criteria defined in UTIL, but something just is not adding up for me...
I have a source pattern table that I need to count only those item performed within the defined period UTIL: ROOM,DOW,BEGIN,END,PROCEDURES MR1,2,08:00,17:00,2 MR1,3,08:00,17:00,3 MR1,4,10:00,17:00,4 MR1,5,09:00,17:00,5 MR1,6,08:00,17:00,6
And the source data table to match EXAM: ROOM,DOW,EXAM_TIME,RQEX_KEY MR1,2,12:27,234 MR1,2,13:17,203 MR1,2,01:24,222 MR1,2,08:03,224 MR1,2,10:20,244
