4 Replies Latest reply: Jan 18, 2016 5:08 AM by Sunny Talwar RSS

    matching the value in the interval

    Kartikeswar Parida

      Hi,

       

      Can someone help me out on this?

       

      I've table with Product, Price & effective date. I've to find out the price on a particular date.

       

      Sample data: 

      ProductPriceEffective From
      P1507-Jan-10
      P17013-Jan-10
      P110025-Jan-10
      P2406-Jan-10
      P28014-Jan-10
      P211024-Jan-10

       

      So if I select 8-Jan-10 then I should get

       

       

      ProductPrice
      P150
      P240

       

      Can it be done in using intervalmatch, if yes can you please give me an example??

       

      Thanks in advance.

        • Re: matching the value in the interval
          Sunny Talwar

          Check this out:

           

          Table:

          LOAD * Inline [

          Product, Price, Effective From

          P1, 50, 7-Jan-10

          P1, 70, 13-Jan-10

          P1, 100, 25-Jan-10

          P2, 40, 6-Jan-10

          P2, 80, 14-Jan-10

          P2, 110, 24-Jan-10

          ];

           

          FinalTable:

          LOAD *,

            Date(If(Previous(Product) = Product, Previous([Effective From]), [Effective From])) as [Effective To]

          Resident Table

          Order By Product, [Effective From] desc;

           

          DROP Table Table;

           

          MinMax:

          LOAD Min([Effective From]) as Min,

            Max([Effective From]) as Max

          Resident FinalTable;

           

          LET vMin = Peek('Min');

          LET vMax = Peek('Max');

           

          DROP Table MinMax;

           

          Calendar: 

          LOAD Date($(vMin) + IterNo() - 1) as Date 

          AutoGenerate 1

          While $(vMin) + IterNo() -1 <= $(vMax);

           

          Link:

          IntervalMatch(Date)

          LOAD [Effective From],

            [Effective To]

          Resident FinalTable;


          Capture.PNG