QlikView - IntervalMatch

QlikView IntervalMatch is a powerful function used to match distinct numeric values to numeric intervals. It is useful in analyzing how the events actually happened versus the planned events. The example of a scenario where it is used is in the assembly lines of the production houses where the belts are planned to run at certain times and for certain duration. However, the actual run can happen at different points in time because of breakdown etc.

Example

Consider an assembly line where there are three belts named A, B and C. They are planned to start & stop at specific times of a day. In a given day, we study the actual start and end time and analyze what all happened in that day. For this, we consider two sets of observations as shown below.

# Data Set for AssembilyLine.
StartTime,EndTime, BeltNo
00:05,4:20, A
1:50,2:45,B
3:15,10:30,C
	
# Data set for the events happened.
ActualTime,Product
1:10,Start Belt A
2:24,Stop Belt A
3:25,Restart Belt A
4:35,Stop Belt A
2:20,Start Belt B
3:11, Stop Belt B
3:15,Start Belt C
11:20, Stop Belt C

Creating the Script

We open the script editor in a new QlikView document using Control+E. The following code creates the required tables as inline data. After creating this script, press control+R to reload the data into the QlikView document.

Creating Sheet Object

Let us create a Table Box sheet object to show the data generated by the IntervalMatch function. Go to the menu item Layout → New Sheet Object → Table Box. The following window appears in which we mention the Title of the table and select the required fields to be displayed.

Showing the Table Box

On clicking OK in the above window, a table appears showing the field ActualTime matched to the intervals StartTime and EndTime.