topic Aggr function in QlikView

Aggr function

qw_johan — Sun, 30 Oct 2011 13:26:51 GMT

Hi,

I am trying to get Aggr() to do my work, but I am having a problem.

I am loading a file into qlikview that holds a lot of timestamps (about 150.000). And

I want to count the number of timestamps in 30 min incraments, like:

Starttime Number Percent of total

0600-0630 312

0630-0700 894

0700-0730 1092

...

With Aggr(count(timestamp_start), timestamp_start) i am close to what I want with the counting.

Does anybody know how I can accomplish this?

Thanks

Aggr function

swuehl — Sun, 30 Oct 2011 14:03:01 GMT

So your timestamp_start is already giving you the appropriate class, or do you look for a way to classify your timestamps into 30 min slots?

If latter, try to add something like

LOAD ..

timestamp(floor(YOURTIMESTAMPFIELD, 1/48 )) as StartTimeStampSlot,

...

FROM ...

If you use this as field in your above aggr(), it should correctly count your Timestamps in each 30 min slot.

Hope this helps,

Stefan

Aggr function

qw_johan — Sun, 30 Oct 2011 14:04:28 GMT

Ok...I did it like this...se below.

But isn't there a better way?

if(starttime<'0600', 'before 0600',

if(starttime>='0600' and starttime<'0630', '0600-0629',

if(starttime>='0630' and starttime<'0700', '0630-0659',

if(starttime>='0700' and starttime<'0730', '0700-0729',

if(starttime>='0730' and starttime<'0800', '0730-0759',

if(starttime>='0800' and starttime<'0830', '0800-0829',

if(starttime>='0830' and starttime<'0900', '0830-0859',

if(starttime>='0900' and starttime<'0930', '0900-0929',

if(starttime>='0930' and starttime<'1000', '0930-0959',

if(starttime>='1000' and starttime<'1030', '1000-1029',

if(starttime>='1030' and starttime<'1100', '1030-1159', 'later than 1100'))))))))))) as my_starttid,

Aggr function

qw_johan — Sun, 30 Oct 2011 14:13:43 GMT

Thanks for your reply Stefan.

I tried to include your code ... timestamp(floor(YOURTIMESTAMPFIELD, 1/48 )) as StartTimeStampSlot

in my load script...but was unsuccessful. No error, just didn't really do anything. I did reload a few times.

What does 1/48 do?

Aggr function

swuehl — Sun, 30 Oct 2011 14:23:36 GMT

Your starttime is not a real Timestamp, it's a string, right? I assumed a timestamp with numerical representation with my above expression.

1/48 is the numerical representation of 30 min (1/48 of a day)).

So, do you have a real timestamp available or just a string representation?

Aggr function

qw_johan — Sun, 30 Oct 2011 14:51:35 GMT

Your're right! My timestamp isn't a real timestamp, it's a string. I have the date in another string too, so I could create a timestamp.

"1/48 is the numerical representation of 30 min (1/48 of a day))"

Oh, ok I understand.

I think I am going to try your approch again...by creating a timestamp and work with that.

No, I don't have a true timestamp available ... I just have the string.... But I could create one.

Aggr function

swuehl — Sun, 30 Oct 2011 15:23:05 GMT

I think you could probably live with both, real timestamps have the advantage that you could easily calculate (e.g. differences) with their numerical representation.

and if you want to get strings for the classes like above (e.g. '1600-1629') you could use something like:

=timestamp(floor(now(), 1/48 ),'hhmm')&'-'&timestamp(ceil(now(),1/48)-1/(24*60),'hhmm')

just replace now() with your Timestamp field then.

having a classification like that, you could then cut to lower and upper limits like in your above snippet (before 0600 and later than 1100 using a somewhat shorter if(statement).

You could also look into class function to get something similar, but I think you have more freedom in formatting doing it like suggested above.

Hope this helps,

Stefan