topic 10 min time interval in QlikView

10 min time interval

Tue, 08 May 2012 10:31:48 GMT

I have the check in time data (ex: 20:02:30) and I'm trying to group them into 15 min intervals to analyze their evolution along the day. Don't know how to fix it.... help will be very appreciated.

Thanks

10 min time interval

m_woolf — Tue, 08 May 2012 11:05:16 GMT

This code rounds to the lowest 30 minutes. Changing the 48 to 96 should round to the lowest 15 minutes.

=floor(frac(STARTTIME)*48)/48+floor(STARTTIME) //QV next lowest half hour (1/48 of day)

10 min time interval

Tue, 08 May 2012 11:12:31 GMT

sorry, I was trying to group them into 10 min interval but 15 min fix it anyway.... I'm a newbie and your code gives me the following error message... "sentence unknown"

thanks so much

10 min time interval

m_woolf — Tue, 08 May 2012 11:18:07 GMT

Replace the 48s with 144s for 10 minutes.

Did you replace STARTTIME with the fieldname of your Timestamp field?

10 min time interval

Tue, 08 May 2012 11:25:52 GMT

One stupid question... where I am supposed to write that code line? Because I am sitll having the same error

Re: 10 min time interval

m_woolf — Tue, 08 May 2012 11:27:39 GMT

In your load script. Did you end the line with a comma?

=floor(frac(STARTTIME)*48)/48+floor(STARTTIME),

10 min time interval

Tue, 08 May 2012 11:51:55 GMT

Maybe I'm forgetting something... I got this:

SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='#.##0,00 €;-#.##0,00 €';
SET TimeFormat='h:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY h:mm:ss[.fff]';
SET MonthNames='ene;feb;mar;abr;may;jun;jul;ago;sep;oct;nov;dic';
SET DayNames='lun;mar;mié;jue;vie;sáb;dom';

LOAD Centro,
     Años,
     [Fecha de documento],
     [Hora], */this is timestamp/*
     [Valor económico]

FROM

\\psf\Home\Desktop\DATOFINAL.xlsx
(ooxml, embedded labels);

=floor(frac([Hora])*48)/48+floor([Hora]),

10 min time interval

m_woolf — Tue, 08 May 2012 11:56:01 GMT

LOAD Centro,

Años,

[Fecha de documento],

[Hora], */this is timestamp/*

floor(frac([Hora])*144)/144+floor([Hora]) as YourNewFieldName,

[Valor económico]

FROM

\\psf\Home\Desktop\DATOFINAL.xlsx
(ooxml, embedded labels);

10 min time interval

Tue, 08 May 2012 12:03:11 GMT

Ok, I get It... but I think it's not what I was looking for. What I want is:

17:23:20 ...

17:24:21 ...

Grouping these timestamps into 17.20-17.30 interval

17:37:21 ...

17:38:21 ...

17.30-17.40 interval....

10 min time interval

m_woolf — Tue, 08 May 2012 12:05:29 GMT

And what do you get with the new field?

10 min time interval

Tue, 08 May 2012 12:10:51 GMT

Numbers like:

0,013888888888889

0,027777777777778

0,034722222222222

0,34722222222222

...

10 min time interval

m_woolf — Tue, 08 May 2012 12:13:43 GMT

You need to format that field as you wish. Something like:

Timestamp(floor(frac([Hora])*144)/144+floor([Hora]),'M/D/YY hh:mm TT') as YourNewFieldName,