topic Re: Creating additional field column by Time of day in QlikView

Creating additional field column by Time of day

Tue, 18 Mar 2014 19:07:35 GMT

I have a data set that I have been able to break down a single column into multiple fields from the load script below.

I now have the ability to drill down by Year, Month, Day, and Time.

My problem is that my Time field contains every time stamp down to the second.

Does anyone know the easiest way to group the Time data so so I can see the results at a specific hour or half hour?

Ideally, I would like time to read

1PM

2PM

3PM

4PM

5PM

etc.

Directory;

LOAD

Year([RequestTime]) as Year,

Month([RequestTime]) as Month,

Day([RequestTime]) as Day,

Time([RequestTime]) as Time;

LOAD

Timestamp(Timestamp#(Mid([RequestTime],2,20),'DD/MMM/YYYY:hh:mm:ss')) as [RequestTime]

FROM

[..\..\20140313_Resource_2.xlsx]

(ooxml, embedded labels, table is resource);

LOAD

[HTTP Status],

[Time to Serve Request (Microseconds)],

FROM

[..\..\20140313_Resource_2.xlsx]

(ooxml, embedded labels, table is resource)

Re: Creating additional field column by Time of day

swuehl — Tue, 18 Mar 2014 19:18:02 GMT

Try something like

Time(round([RequestTime],1/24),'hh TT') as Time;

Re: Creating additional field column by Time of day

Tue, 18 Mar 2014 20:00:38 GMT

Am I able to break this down by every 5 minutes?

Re: Creating additional field column by Time of day

swuehl — Wed, 19 Mar 2014 18:51:59 GMT

Above will round to the nearest hour of the day (24 hours a day, thus 1/24), if you want to round to nearest 5 minutes, there are 12 five minutes intervals per hours, so you need to use

Time(round([RequestTime],1/(24*12)),'hh:mm TT') as Time