group hours by half hour interval : good practice script ?

[brunobertels]

Hi community

I have a timestamp field named (@1) with this format "02/09/2015  08:36:24"

using hour() function i created a hour dimension to dispatch events by hour

Nethertheless I need in fact group hour interval like below :

8:30 to 9:30

9:30 to 10:30

10:30 to 11:30

11:30 to 12:30

and

13:30 to 14:30

14:30 to 15:30

15:30 to 16:30

16:30 to 17:30

Events before 8:30 should be attached to the 8:30 to 9:30 group

Events between 12:30 to 13:00 should be attached to 11:30 to 12:30 group

Events between 13:00 to 13:30 should be attached to 13:30 to 14:30 group

Then Events after 17:30 should be attached to 16:30 to 17:30 group

to achieve this requirments in my script i put this and it's works well

	// test group by tranche horaire
	if(right(@1,8)<='09:30:00','8h30 à 9h30',
	if(right(@1,8)<='10:30:00','9h30 à 10h30',
	if(right(@1,8)<='11:30:00','10h30 à 11h30',
	if(right(@1,8)<='13:00:00','11h30 à 12h30',
	if(right(@1,8)<='14:30:00','13h30 à 14h30',
	if(right(@1,8)<='15:30:00','14h30 à 15h30',
	if(right(@1,8)<='16:30:00','15h30 à 16h30',

    if(right(@1,8)>'16:30:00','16h30 à 17h30','autres'))))))))as "Interval",

So is this solution a good one or is there another way to do the same ?

Thanks in advance for your help

Bruno

[sinanozdemir]

Hi Bruno,

This is as close as I can get to what you are looking for by using Class() function:

I know it is not exactly what you are looking for, but I just wanted to throw my 2 cents.

[markodonovan · Accepted solution]

Hi Bruno,

Try using intervalmatch:

testdata:

LOAD * INLINE [

    id,starttime,endtime , label

    1,09:00,9:30,09:00 to 9:30

    2,10:00,10:30,10:00 to 10:30

];

timeintervals:

LOAD * INLINE [

time

09:01

09:20

10:21

];

left join(testdata)

IntervalMatch(time)

LOAD Distinct

    starttime,

    endtime

Resident testdata;   

This might help if you have never used intervalmatch before:

https://youtu.be/MZzG2MIcq8o

Thanks

Mark

http://techstuffy.tv

[jagan]

HI,

Try like this

Master Calendar with every 30 Minutes

Regards,

Jagan.

[markodonovan]

Hi Bruno,

Must admit you probably get better performance during the reload (if the table is very large)

using a time calendar.

Just shows that there is often more than 1 way to solve a problem in QlikView.

Mark

http://techstuffy.tv

[brunobertels]

Hi Sinan

Thanks for your help , I read another thread about the class function. With your exemple a know how to adapt this to my app

Regards,

Bruno

[jagan]

Hi,

Try like this

LOAD

*,

Timestamp(Round(Num(Now()), 1/48), 'hh:mm') & ' - ' & Timestamp(Round(Num(Now()), 1/48) + 1/24, 'hh:mm') AS TimeRange

FROM DataSource;

[brunobertels]

Thanks Mark

It works perfectly

Bruno

[sinanozdemir]

You are welcome.

[brunobertels]

Hi Jagan

Not exactly my requirement for this project but very usefull for another APP in the futur

Thanks for your help

Bruno