Working between times

[Anonymous]

Hi

I have a group of staff with shifts.  I am trying to create a chart that displays how many staff I have on duty for different times of the day.

The relevant fields I have the staff red,Date, Shift start time, and Shift end time.

I need the chart to show how many staff are on duty at 7am, 8am, 9am, 10am ....... for any given date

How can this be achieved ???

Any help appreciated.  (please go easy on me as I am a bit of a novice)

Thanks

Phil

[hic]

What you want to do is similar to what I show in the blog post on http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/12/reference-dates.

The time can be created using something similar to

     Time(( FromTime + IterNo() – 1)/24) as ReferenceTime

HIC

[#3]

Hi,

Check this attch file and let me know

Regards,

Ashutosh

[Anonymous]

Thanks.  I thought you had cracked it but there is a problem.

This works great if the staff are working during the day, however it returns a null if the staff are working overnight.

i.e.  start 19:00 & finish 07:00

Any thoughts?

[#5]

hi,

     can you please mention whose code is working fine for you. then we can give reply accordingly.

Thanks,

Ashutosh

[Anonymous]

Apols

Ashutosh

Your code is working, for during the day, but not if starting before midnight, and finishing after midnight.

Regards

Phil

[#7]

Hi,

If you want to finish shift after midnight it means you entered in next day. but this one is only work for current day.

If your requirement is for midnight then give some flag value and separate midnight shift and day shift first and then use same date format for midnight like day shift then it will work for both.

please check this information also and let me know.

Thanks,

Ashutosh

[Anonymous]

Ashutosh

Could you give me an example of what you mean?

Regards

Phil

[Anonymous]

I had and idea as to how to get it working, but I just can't get it to run. 

What am I doing wrong?

I don't know how to attach file so I have copied script.

OrderLogTemp:

LOAD Date,
Start,
End,
Staff,
Floor(((if(End<Start,End+1,End))*24)) as True_End,
Floor(((Date+Start)-Date)*24) as True_Start

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);


OrderLog:
LOAD Date,
Start,
End,
Staff,
True_Start,
True_End

Resident OrderLogTemp;
Drop Table OrderLogTemp;

EventLog:
LOAD * INLINE [
Time,Comment
07:00,Start shift 1
08:00,Start shift 2
09:00,Start shift 3 and End of shift 1
10:00,Start shift 4 and End of shift 2
11:00,End of shift 3 and End of shift 3
12:00,End of Shift 4 and End of shift 4
]
;

IntervalLog:
IntervalMatch(Time) load Start,End

Resident OrderLog;

Phil

[Anonymous]

I Plan to update Time Load to it looks at Interval, (as below)

IntervalLog:

IntervalMatch(Time) load True_Start_Time,True_End_Time

Resident OrderLog;

I cant get that faras the load fails