Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On Demand Webinar: See Why Thousands of QlikView Users Have Switched to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
kmarsden
Partner
Partner

peek()? generate a line based on previous

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
1 Solution

Accepted Solutions
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

View solution in original post

2 Replies
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

View solution in original post

kmarsden
Partner
Partner

This does exactly what I needed. I removed the "+1/24/60/60" from the FromDatetime and ToDatetime fields and it is spot on. Thank you Henric, you saved me pulling my hair out!