2 Replies Latest reply: Dec 20, 2011 4:10 PM by milos.vukic RSS

    Group By Problem

      Hello,

       

      I am new to in QV world and trying to put together a small analytics dashboard. I am trying to count records (activities) created by different users and to group them by 5 minutes period starting 2009-01-01 00:00:00, for each user and each activity. In the code below I am using just one activity for testing. The records get counted and grouped by user but I cannot get them grouped by 5 minute slots.

      Please advise. (qvd enclosed)

       

      TempTable1:

      LOAD

          [Note Created By]as CreatedBy,

          'Note Created' as Activity,

          Interval(([Note Created On] - '2009-01-01 00:00:00')/300,'S') as CreatedOn_SSDiff,

          [Note Created On] as StartingDate

      FROM

      $(vProjectQvdDir)Notes.qvd (qvd);

       

      TempTable2:

      LOAD

           CreatedBy,

           Activity,

           Min(Date(StartingDate,'YYYY-MM-DD hh:mm:ss')) as SD,

           CreatedOn_SSDiff,

           Count(Activity)

       

      Resident TempTable1

       

      Where Year(StartingDate)=1995

       

      Group by

      CreatedBy,

      Activity,

      CreatedOn_SSDiff;

       

      Drop Table TempTable1;

        • Re: Group By Problem
          Rob Wunderlich

          One problem may be that you are using [Note Created On] which doesn't include the time. I think you should use [Note Created On Full]. To assign to 5 minute buckets relative to 2009-01-01 how about:

           

          Interval(

            interval#(

              floor((([Note Created On Full] - MakeDate(2009)) * 86400), 300)

              ,'s'

            )

          ,'d hh:mm') as CreatedOn_SSDiff

           

          -Rob