half hour intervals

[anjali0108]

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 .

[sunny_talwar]

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

];

[anjali0108]

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 .

[Oleg_Troyansky]

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 an...

[sunny_talwar]

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

[MarcoWedel]

Hi,

maybe you could also use the class() function.

hope this helps

regards

Marco