5 Replies Latest reply: Feb 7, 2012 8:09 PM by Graham Crooks RSS

    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.

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

          Hi,

               Try using interval match function.

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

           

          Celambarasan

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

            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.

              • Join a value from Table2 to Table1 where Date in Table 1 falls inside a range in Table 2
                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.   

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

                    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

                      • Re: Join a value from Table2 to Table1 where Date in Table 1 falls inside a range in Table 2
                        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