3 Replies Latest reply: Mar 29, 2012 2:54 PM by Karl Pover RSS

    How to lookup a value based on two dimensions

      Hi,

       

      I'd like to ask, it it is possible to lookup values from a table based on two dimension. The problem is that the team is linked to a time period. Is it possible to link the Team name from Table1 to Table2 based on the Cost center and date? Here is a simple example of the 2 tables:

      Tab1:

      CcenterValid from DateValid to DateTeam
      PA01.01.200631.12.2006Team1
      PA01.01.200731.12.2007Team1
      PA01.01.200831.12.2008Team1A
      PA01.01.200931.12.2009Team1A
      PA01.01.201031.12.2010Team1A
      PB01.01.200631.12.2006Team2
      PB01.01.200731.07.2007Team2
      PB01.08.200831.12.2008Team2
      PB01.01.200931.12.2009Team2A
      PC01.01.200731.12.2007Team3
      PC01.01.200831.12.2008Team3A
      PC01.01.200931.12.2009Team3A
      PD01.01.200631.12.2006Team4
      PD01.01.200731.12.2007Team4
      PD01.01.200831.12.2008Team4
      PE01.01.200631.12.2006Team5
      PE01.01.200731.12.2007Team5

       

      Tab2:

      CcenterMonthYear
      PA112006
      PA52007
      PA62007
      PA72007
      PA12010
      PB72006
      PB92008
      PB112008
      PB22009
      PC52007
      PC72007
      PC122009
      PD22007


      Thx

        • How to lookup a value based on two dimensions
          Karl Pover

          You can use an intervalmatch() function.

           

          First load both tables and change Tab2 to have a date with a

           

          Load [Valid From Date],

               [Valid To Date],

               Ccenter,

               Team

          From Tab1;

           

          Load Ccenter,

          makedate(Year,Month) as Date

          From Tab2;

           

          Then do an intervalmatch

           

          Intervalmatch(Date,Ccenter)

          Load [Valid From Date],

               [Valid To Date],

               Ccenter

          Resident Tab1;

           

          Hope that works for you.

           

          Karl

            • How to lookup a value based on two dimensions

              Hi Karl,

               

              thanks for your advice, the intervalmatch worked fine, but there is still that problem that I'm getting all combinations of the departments with the one period. As example:

               

              PA112006

               

              for this cost center, year and period I have two lines

              first with the Team1

              second with the Team1A

               

              based on the Intervals it should be only line with the Team1.

               

              Is it possible to restrict the duplication of the lines and to show only the correct one?

               

              Thanks

              Lukas