Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

philmywallet
New Contributor III

Working between times


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

Tags (3)
10 Replies
Employee
Employee

Re: Working between times

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

Not applicable

Re: Working between times

Hi,

Check this attch file and let me know

Regards,

Ashutosh

philmywallet
New Contributor III

Re: Working between times

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?

Not applicable

Re: Working between times

hi,

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

Thanks,

Ashutosh

philmywallet
New Contributor III

Re: Working between times


Apols

Ashutosh

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

Regards

Phil

Not applicable

Re: Working between times

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

philmywallet
New Contributor III

Re: Working between times


Ashutosh

Could you give me an example of what you mean?

Regards

Phil

philmywallet
New Contributor III

Re: Working between times

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

philmywallet
New Contributor III

Re: Working between times

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

Community Browser