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.
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:
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
IntervalMatch(RptDate, Measure) //extended snytax of intervalmatch
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.