topic Re: Group By Problem in QlikView

Group By Problem

Tue, 20 Dec 2011 18:13:24 GMT

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

rwunderlich — Tue, 20 Dec 2011 19:32:21 GMT

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

Re: Group By Problem

Tue, 20 Dec 2011 21:08:43 GMT

Thank you Sir!

I was a bit confused as it gave me correct time slots (in seconds) when I was to pull the information into a table or chart.