Analysing By Date Range

[#1]

I'm new to QV, looking for some advice on the following;

I have 2 tables containing a RATE per hour and the HOURS worked, linked by a common ID field.

TABLE 1
ID	START_DATE	END_DATE	RATE
9	01-JAN-13	04-JAN-13	$50
9	05-JAN-13	31-JAN-13	$55

TABLE 2
ID	DATE	HOURS
9	12-JAN-13	4

How can I select the appropriate RATE in TABLE 1 to match the HOURS worked in TABLE 2 on a certain date to give me a value ?

In the example, employee 9 worked 4 HOURS on the 12-JAN-13 and this equates to a RATE of $55, (as 12-JAN-13 falls between the START_DATE 05-JAN-13 and END_DATE 31-JAN-13) which is $220 (4 HOURS * $%% per hour).

Any advice welcome.

[#2 · Accepted solution]

I love answering my own questions !

Search for IntervalMatch in this forum. Great article here by Henric Cronstrom;

(http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/04/intervalmatch