2 Replies Latest reply: Sep 25, 2015 8:55 AM by Kevin Marsden RSS

    peek()? generate a line based on previous

    Kevin Marsden
      StudentIDAbsenceFromDatetimeToDatetime
      1Sick01/01/2015 00:00:0001/01/2015 23:59:59
      1Sick04/01/2015 00:00:0005/01/2015 23:59:59
      1Exam07/01/2015 00:00:0007/01/2015 23:59:59
      2Exam07/01/2015 00:00:0007/01/2015 23:59:59

       

       

      LOAD

      StudentID, Absence, FromDatetime, ToDatetime

      FROM

       

      $(vPath)\Diary.qvd
      (qvd) ;

       

      Hi, I have an issue where I'm trying to evaluate the above table to find instances where StudentID AND Absence are the same as the previously loaded line. When this occurs I need to generate a line of data for the same StudentID but with the following:

      • an Absence of 'Attending'
      • a FromDatetime of 1 second after the ToDatetime on the previously loaded line
      • a ToDatetime of 1 second after the ToDatetime on the previously loaded line (i.e. the same as FromDatetime above)

       

      I'm assuming peek() should be used to evaluate StudentID and Absence which I'm fine with. However, I'm struggling to generate the additional line whenever the criteria is met. Any help would be greatly appreciated...

       

      Example of required output would be:

       

      StudentIDAbsenceFromDatetimeToDatetime
      1Sick01/01/2015 00:00:0001/01/2015 23:59:59
      1Attending02/01/2015 00:00:0002/01/2015 00:00:00
      1Sick04/01/2015 00:00:0005/01/2015 23:59:59
      1Exam07/01/2015 00:00:0007/01/2015 23:59:59
      2Exam07/01/2015 00:00:0007/01/2015 23:59:59
        • Re: peek()? generate a line based on previous
          Henric Cronström

          Try

           

          Load
             RowNo() as RowNo,
             StudentID,
             ShouldBeDuplicated,
             If(IterNo()=1 and ShouldBeDuplicated,'Attending',Absence) as Absence,
             If(IterNo()=1 and ShouldBeDuplicated,TimeStamp(Previous(ToDatetime)+1/24/60/60),FromDatetime) as FromDatetime,
             If(IterNo()=1 and ShouldBeDuplicated,TimeStamp(Previous(ToDatetime)+1/24/60/60),ToDatetime) as ToDatetime
             While IterNo() =1 or (IterNo()=2 and ShouldBeDuplicated);
          Load *,
             StudentID=Previous(StudentID) and Absence=Previous(Absence) as ShouldBeDuplicated
            From … ;


          HIC