topic Re: How to lookup value in between dates? in QlikView

How to lookup value in between dates?

bimotive — Tue, 16 Feb 2016 19:43:47 GMT

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

Re: How to lookup value in between dates?

Oleg_Troyansky — Tue, 16 Feb 2016 20:03:00 GMT

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!

Re: How to lookup value in between dates?

hic — Tue, 16 Feb 2016 20:30:12 GMT

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

Re: How to lookup value in between dates?

bimotive — Tue, 16 Feb 2016 20:41:15 GMT

Hi Henric (Oleg too),

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

Arjen