How to lookup value in between dates?

[bimotive]

Hello,

I can't get around to get this thing done. The situation is as follows;

Table1:

ProjectID,

StartDate,

EndDate,

HourRate,

Table2:

ProjectID,

Date,

ActualHours,

I have to calculate the (ActualHours * HourRate)  but with the correct HourRate for the date in Table2

(in between start- and EndDate from Table1).

Can somebody help me out? Thx in advance...

Arjen

[Oleg_Troyansky]

Look up the term INTERVALMATCH, that's the solution to this issue.

cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Milan, Italy!

[hic]

This is a slowly changing dimension, that you should solve with an intervalmatch. See

Slowly Changing Dimensions

IntervalMatch

Try something like the following:

Intervals:

Load

ProjectID,

StartDate,

EndDate,

ProjectID & '|' & StartDate  & '|' & EndDate as IntervalID,

HourRate

From ... ;

Dates:

Load

ProjectID,

Date,

ProjectID & '|' & Date as DateID,

ActualHours

From ...;

tmp;

IntervalMatch (Date,ProjectID)

Load

StartDate,

EndDate,

ProjectID

Resident Intervals;

Bridge:

Load

ProjectID & '|' & StartDate  & '|' & EndDate as IntervalID,

ProjectID & '|' & Date as DateID

Resident tmp;

Drop Table tmp;

Drop Field ProjectID From Dates;

// HIC

[bimotive]

Hi Henric (Oleg too),

Now that I see your solution I understand and thank you for your guidance!

Arjen