topic Re: half hour intervals in QlikView

half hour intervals

anjali0108 — Wed, 21 Oct 2015 12:37:57 GMT

Hi All,

I have a date time field as :-

Call Start Time

10/18/2015 12:12

10/18/2015 13:05

10/18/2015 14:42

10/18/2015 14:52

10/18/2015 15:06

10/18/2015 15:27

10/18/2015 15:38

In this,I want to divide a day into half hour slots like 11:30-12:00,12-12:30 etc.

And I want the calls who have call start time falling in that particular half hour interval to be counted .

Example 5 calls have been logged in the interval 11:30-12:00 .

Please suggest .

Re: half hour intervals

sunny_talwar — Wed, 21 Oct 2015 12:42:00 GMT

May be this:

SET TimestampFormat='M/D/YYYY hh:mm';

Table:

LOAD [Call Start Time],

Time(Floor([Call Start Time], (1/48)), 'hh:mm') & ' - ' & Time(Ceil([Call Start Time], (1/48)), 'hh:mm') as Interval;

LOAD * Inline [

Call Start Time

10/18/2015 12:12

10/18/2015 13:05

10/18/2015 14:42

10/18/2015 14:52

10/18/2015 15:06

10/18/2015 15:27

10/18/2015 15:38

];

Re: half hour intervals

anjali0108 — Sun, 25 Oct 2015 17:06:19 GMT

Heyy ,thnqeww soo much for your reply ,it is really helpful .

Could you please tell :-

As Call start time is not a static field ,it will keep on increasing as the data will increase ,so is there any other way of doing this load * inline expression because I can't specify all the values here

under call start time .

Thanks in advance .

Re: half hour intervals

Oleg_Troyansky — Sun, 25 Oct 2015 21:22:13 GMT

The INLINE load here was used just as an example, to illustrate the use of the functions Floor() and Ceil() with timestamps. Once you understand how they work, you can apply this logic to your own loads.

cheers,

Oleg Troyansky

Check out my new book QlikView Your Business: An expert guide to Business Discovery with QlikView and Qlik Sense

Re: half hour intervals

sunny_talwar — Mon, 26 Oct 2015 00:02:10 GMT

As Oleg has already pointed out that this is just me working with sample data, but with your original data you would do something like this:

SET TimestampFormat='M/D/YYYY hh:mm';

Table:

LOAD [Call Start Time],

Time(Floor([Call Start Time], (1/48)), 'hh:mm') & ' - ' & Time(Ceil([Call Start Time], (1/48)), 'hh:mm') as Interval;

LOAD yourFieldsIncludingStartTimeField

FROM yourDataSource;

HTH

Best,

Sunny

Re: half hour intervals

MarcoWedel — Mon, 26 Oct 2015 00:08:06 GMT

Hi,

maybe you could also use the class() function.

hope this helps

regards

Marco