Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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:
Thanks
Mark
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.
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:
Thanks
Mark
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
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
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;
Thanks Mark
It works perfectly
Bruno
You are welcome.
Hi Jagan
Not exactly my requirement for this project but very usefull for another APP in the futur
Thanks for your help
Bruno