Join a value from Table2 to Table1 where Date in Table 1 falls inside a range in Table 2

[graham-crooks]

Hi. Here's the scenario...

I've a table (A) that has data according to:

Table A:

Load

     KPIKey,

     RptDate,

     ResultValue

     ...

where RptDate is the YearMonth of the data being reported (hence 12 months worth shows me the annual trend / performance).

Table B contains the stated targets the data in Table A should match. But, the targets can change with time, so Table B is structured:

Table B:

Load

     KPIKey,

     TargetDateFrom,

     TargetDateTo,

     TargetValue.

I need to get the right TargetValues lined up with the right ResultValues in Table A, where RptDate falls in the correct range i.e. between the TargetDateFrom and TargetDateTo values:

     RptDate >= TargetDateFrom and RptDate <= TargetDateTo.

I was thinking that a left join of some sort must be the way, but please can anyone advise: how do I load the data from Table B according to the criteria above? As RptDate is not a field in Table B putting this criteria in a simple where clause for the Table B load obviously won't work.

Any assistance most welcome.

[CELAMBARASAN]

Hi,

     Try using interval match function.

          IntervalMatch (RptDate,KPIKey) Load TargetDateFrom, TargetDateTo, KPIKey resident Table B;

Celambarasan

[#3]

Hi Try this!

Thru Applymap() function and with the help of Key u can get RptDate in table B.

Now take resident of the above table B with the Where Condition.

[graham-crooks]

Hi

Just to clarify, it's the TargetValue value I need to get into Table A; the choice of which particular one, however, depends on which target value was appropriate at the time the data represents - RptDate gives me the month the data refers to, whilst the TargetDateFrom and To ranges show how the target varies over time. RptDate should therefore fall naturally inside one of the date ranges for a specific KPIKey. If I can determine which one, then that gives me the target that was prevalent at the time. As an example:

KPI     RptDate     Result          [Would match:]

1          May          4.1               [3.5]

1          June          3.9               [4.2]

1          July          4                    [4.2]

KPI     TargetFrom     TargetTo     TargetValue

1          April               May               3.5

1          June               October          4.2

Thus for the July result for KPI 1, the target would naturally be 4.2, since July falls in the range June to October and the target during this period was 4.2. That's what I need to hook-up.

Cheers.   

[#5]

Hi!

we could have used applymap func if KPIkey was unique.

But here the case is differnt....let me think for such a case.

Regards

Erika Jain

[graham-crooks]

Hi Erika

I have found a solution to the problem. By chance I came across: http://community.qlik.com/message/189043#189043

The solution utilises IntervalMatch (as Celambarasan suggested). Here's a code example that illustrates the idea:

//My version

TableA:     //the target values

LOAD * Inline

[

Measure, Target, DateFrom, DateTo, TargetQualifier

M3, 2.4, 01/04/2011, 31/03/2011, 'Other Target'

M4, 3.2, 01/04/2010, 31/03/2011, 'Old Target'

M4, 4.4, 01/04/2011, 31/12/9999, 'New Target'

M5, 5.4, 01/04/2011, 31/12/9999, 'Another Target'

];

TableB:     //the report data

LOAD * Inline

[

Measure, RptDate, Result

M3, 30/06/2011, 2.6

M4, 28/02/2011, 3.3

M4, 31/03/2011, 3.7

M4, 30/04/2011, 3.9

M4, 31/05/2011, 4.5

M4, 30/09/2011, 4.8

M5, 31/10/2011, 5.2

M6, 31/10/2011, 6.3

];

Left Join(TableB)

IntervalMatch(RptDate, Measure)     //extended snytax of intervalmatch

LOAD

          DateFrom,

          DateTo,

  Measure

Resident TableA;

Left Join(TableB)

LOAD

          Measure,

          DateFrom,

          DateTo,

          Target,

          TargetQualifier

Resident TableA;

DROP Table TableA;

DROP Fields DateFrom, DateTo;

The extended syntax allows key fields to accompany the range fields, thus creating the neccessary link between tables... and all is well. Big respect for the Community.

And to Erika, thank you for taking the time to consider this problem.

Cheers

Graham