1 Reply Latest reply: Apr 21, 2017 7:52 AM by Sunny Talwar RSS

    Interval Match

    RAM MUTHIAH M

      I have one data to find the Interval match : Can anyone help in this regard?

               

      ItemOdateCrnyCost
      115/1/2012EUR14
      215/2/2012USD15
      315/3/2012EUR16
      415/8/2012USD

      10

       

      Another data

         

      CrnyFromTo

      rent

      USD1/1/201230/4/20121.25
      USD1/6/201230/12/20121.29
      EUR1/1/201231/1/20121.23
      EUR2/2/201230/4/20122.25

       

      The result should be

       

      ItemODateCrnyFromToCostrent
      11/15/2012EUR1/1/20121/31/2012141.23
      22/15/2012USD1/1/20124/30/2012151.25
      33/15/2012EUR2/2/20124/30/2012162.25
      48/15/2012USD6/1/201212/30/2012101.29
        • Re: Interval Match
          Sunny Talwar

          Try this

           

          Table1:

          LOAD * INLINE [

              Item, Odate, Crny, Cost

              1, 15/1/2012, EUR, 14

              2, 15/2/2012, USD, 15

              3, 15/3/2012, EUR, 16

              4, 15/8/2012, USD, 10

          ];

           

          Table2:

          LOAD * INLINE [

              Crny, From, To, rent

              USD, 1/1/2012, 30/4/2012, 1.25

              USD, 1/6/2012, 30/12/2012, 1.29

              EUR, 1/1/2012, 31/1/2012, 1.23

              EUR, 2/2/2012, 30/4/2012, 2.25

          ];

           

          Left Join (Table1)

          IntervalMatch(Odate, Crny)

          LOAD From,

            To,

            Crny

          Resident Table2;

           

          Left Join (Table1)

          LOAD *

          Resident Table2;

           

          DROP Table Table2;