Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Hi,
Try using interval match function.
IntervalMatch (RptDate,KPIKey) Load TargetDateFrom, TargetDateTo, KPIKey resident Table B;
Celambarasan
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.
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.
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
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