Aggr function

[qw_johan]

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

[swuehl]

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

[qw_johan]

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,

[qw_johan]

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?

[swuehl]

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?

[qw_johan]

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.

[swuehl · Accepted solution]

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