1 Reply Latest reply: Nov 3, 2013 1:08 PM by Gysbert Wassenaar RSS

    order by issue when using intervalmatch

    Aissam Boumejjane

      Hi Guys,

       

      I'm having a problem with the 'order by' when I use the interval match function.

      I've created a script that peeks to the previous date of an employee that called called sick.

       

       

      However after using the interval match function the Order by seem not to be working right.

       

      The other problem is that when I peek to the previous date of an employee, it doesn't work 100%.

      For example if I look at the first record it can't peek to the previous loaded record, since there is no previous date available before the first date some one called sick, so null should be returned.

       

      Please look at my script.

       

      Hope some one can help!

       

      Cheers!

       

      Sam

        • Re: order by issue when using intervalmatch
          Gysbert Wassenaar
          IntervalMatch: // <-- meaningless since the result is joined to Tmp
          left join(Tmp)
          IntervalMatch (datum.KEY)
          Load distinct startdate, endDate resident Tmp
          Order by startdate; // <-- doesn't do anything because of the join
          

           

          When you join it with Tmp the order by clause is meaningless. If you want to reorder Tmp to calculate PreviousStartdate and PreviousNumberofDays then you need to do that afterwards:

           

          x:
          NoConcatenate LOAD
          *,
          Peek(startdate,-1) as PreviousStartdate,
          peek(numberofdays,-1) as PreviousNumberofDays
          Resident Tmp
          order by startdate;
          
          drop table Tmp;