2 Replies Latest reply on Oct 13, 2016 3:42 PM by Kris Hallis

    Lookup function creating duplicates

    Kris Hallis

      Hi,

      I am currently working on an attendance workbook for my school. I want one of the views to identify pupils who are absent in roll call but present in period 1.

      Each row in the data source represents a particular pupil on a particular day for a particular period.

       

      I have two calcs:

       

      [Absent Per 0 Calc]

      IIF([Attendance Period]=0 and [Attended Flag Integer] = 0,1,0)

       

      [Present Per 1 Calc]

      IIF([Attendance Period]=1 and [Attended Flag Integer] = 1,1,0)

       

      I then have a lookup to get the difference between the two, which I want to return '1' when pupils are absent in roll call but present in period 1.

       

      [Absent Roll Call - Present Per 1]

      attr([Absent Per 0 Calc]) - lookup(attr([Present Per 1 Calc]), 1)

       

      However, when I move it to the view, it duplicates the data source rows and simply doesn't work. I've tried a number of things, including changing the 'Compute using..', but to no avail.

      Eventually, I want to end up with a view that contains a set of pupils who have been absent in roll call but present in period 1, and a count of how many times that has occured.

       

      I have attached an example packaged file and would very much appreciate any help.

       

      Thanks very much,

      Kris