topic Re: Grouping Timestamp in QlikView

Grouping Timestamp

Fri, 17 May 2013 07:00:03 GMT

I have a question regarding timestamp.

How can i group the time into 1 hour slot?

I may have data at

9.01am

9.02am

.....

9.55am

10.00am

...

10.45am

11am

...

In this case, i would like to have a new column with the new timestamps

9am

10am

11am

thanks!

Re: Grouping Timestamp

sushil353 — Fri, 17 May 2013 07:03:08 GMT

check out the interval match function and using mapping table for the solution.

Re: Grouping Timestamp

kumarnatarajan — Fri, 17 May 2013 07:12:33 GMT

Hi,

Try this one.

=Date(Date#('9.11 PM', 'hh.mm tt'),'hh tt')

Add your load script below mentioned syntex

Date(Date#(FieldName, 'hh.mm tt'),'hh tt') as [New Time]

Re: Grouping Timestamp

Fri, 17 May 2013 07:23:33 GMT

Thanks. I dont know why, but

Date(Date('9.11 PM', 'hh.mm tt'),'hh tt')

seems to work instead. i have added this to my calculated dimension.

Re: Grouping Timestamp

Fri, 17 May 2013 07:26:27 GMT

But now i have another question, does it just format? I would like it to be a new field, so that when i chart, i can chart against this dimension. May i know how to do that?

or do i have to add this expression to the load script?

Re: Grouping Timestamp

kumarnatarajan — Fri, 17 May 2013 07:26:27 GMT

Hi,

This syntex i posted for just example. you don't need that. use second one. you got a result

Re: Grouping Timestamp

Fri, 17 May 2013 07:31:39 GMT

try this this works fine according to your requirment.

hope this helps you.

time1:

LOAD text(date(Date#(timefield,'hh.mmTT'),'hhTT')) as timenew,* INLINE [

timefield

9.01am

9.02am

9.03am

9.55am

10.00am

10.45am

11.00am

];

Re: Grouping Timestamp

Fri, 17 May 2013 08:03:20 GMT

after i have added to the load script, when i chart a table, i have something like this.

21/01/2012 09 sum(field)

21/01/2012 10 sum(field)

21/01/2012 11 sum(field)

why wont qlikview recognise the 1st 4 views as a same dimension? how can i get

21/01/2012 09 sum(field)

21/01/2012 10 sum(field)

21/01/2012 11 sum(field)

thanks!

Re: Grouping Timestamp

Fri, 17 May 2013 08:08:04 GMT

try this

Aggr(sum(field),numberfield)

here numberfield is the fieldname of your 2nd dimension.

Re: Grouping Timestamp

Wed, 21 Aug 2013 08:46:04 GMT

can i have dynamic time slots based on user preference?