2 Replies Latest reply: Feb 11, 2015 9:35 AM by Vinay kumar Bangari RSS

    Interval match and Peek function.

      Hi all,

      I need assistance with interval match and peek function.

       

      Given below are table 1 & table 2 we have four fields in table 1 and the requirement is to insert the date

      field of table 2 with the start date & end date fields of table 1 using interval match such that you

      join the two tables and the output table should contain the fields work center, date , capacity where only unique

      records of work center without duplication.


      Work centerStart dateend datecapacity
      AA1/1/20141/12/2014100
      AA1/14/20141/24/2014200
      BB1/8/20141/17/2014100
      BB1/19/20141/24/2014200

                               table: 1

       

                                

      Work centerDate
      AA1/10/2014
      AA1/16/2014
      AA1/19/2014
      BB1/10/2014
      BB1/16/2014
      BB1/19/2014

                table:2

       

      Other requirement is from the table below, i need to get the last working date of the work center from start date by comparing the next record using interval match and peek function.

       

      Work centerStart datecapacity
      AA1/1/2014100
      AA1/22/2014200
      BB1/8/2014100
      BB1/25/2014200

       

      the output table should be like

      Work centerStart dateLast dateCapacity
      AA1/1/20141/21/2014100
      AA1/22/20141/22/2014200
      BB1/8/20141/24/2014100
      BB1/25/20141/25/2014200

       

      vinaybangari

      Your assistance will be highly appreciated.

        • Re: Interval match and Peek function.

          Hi Abhinav,

           

          I worked out solution for your problem. Please see the attached .qvw. Please mark it as answered to close this thread if you find the solution helpful.

           

          Regards

          • Re: Interval match and Peek function.
            Vinay kumar Bangari

            please use below script and change the path of the excel sheets that should solve your issue:

             

            Final:

            LOAD [Work center],

                Date

            FROM

            [C:\Users\kumarv\Desktop\Interval match with no end date.xlsx]

            (ooxml, embedded labels, table is Sheet1);

             

             

             

             

            Temp_Capacity:

            LOAD [Work center],

                [Start date],

                capacity

            FROM

            [C:\Users\kumarv\Desktop\Interval match with no end date.xlsx]

            (ooxml, embedded labels, table is Sheet2);

            //

            //

             

             

            Cap2:

            LOAD

                [Start date],

                if(len(Peek([Start date]))=0 or [Work center]<>peek([Work center]),'1/1/2020',date(peek([Start date])-1)) as End_date,

                [Work center],

                capacity

              

            Resident Temp_Capacity

             

             

             

            order by [Work center], [Start date]  desc;

             

             

            drop table Temp_Capacity;

             

             

            left join (Final)

            IntervalMatch(Date,[Work center])

            LOAD

                [Start date],

                  End_date,

                [Work center]

              

              

            Resident Cap2 ;