3 Replies Latest reply: Apr 12, 2017 11:50 AM by Sunny Talwar RSS

    Aggr() creating slightly incorrect results

    Philip Zeiser

      I am trying to count the number of jobs my company runs in a given year. We use the same job number over multiple days (or weeks), so I only want to count each job number the for the first date it was used. I have successfully done this in the data load script, but I am getting slightly incorrect results using the Aggr() function. Here is what I am doing and the results.

       

      Data Load:

      ProductionData:

      LOAD JobDate,

          Year(JobDate) AS JobYear,

          Text(Month(JobDate))&'-'&Text(Year(JobDate)) AS JobMonthYear,

          JobNo;

      SQL SELECT JobDate,

          JobNo

      FROM prProdControl

      WHERE JobDate >= '2010-01-01';

       

      Counter:

      Load

          Text(Month(MinJobDate))&'-'&Text(Year(MinJobDate)) AS JobMonthYear,

          Count(JobNo) AS JobCount

      Group By Text(Month(MinJobDate))&'-'&Text(Year(MinJobDate));

       

      Load

          Min(JobDate) AS MinJobDate,

          JobNo

      Resident ProductionData

      Group By JobNo;

       

      Table:

      4b1c10920d28483ab28e1e6e6f573a93.png

      As you can see, the total counts are the same, but there are small differences. I have verified externally that the Sum(JobCount) results are correct. I have tried everything I can think of and I can't find where these variances are coming from. Thanks in advance.