Discussion Board for collaboration on QlikView Scripting.
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)
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
Check this attch file and let me know
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
can you please mention whose code is working fine for you. then we can give reply accordingly.
Your code is working, for during the day, but not if starting before midnight, and finishing after midnight.
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.
Could you give me an example of what you mean?
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.
LOAD Date, Start, End, Staff,Floor(((if(End<Start,End+1,End))*24)) as True_End,Floor(((Date+Start)-Date)*24) as True_StartFROM(txt, codepage is 1252, embedded labels, delimiter is ',', msq);OrderLog:LOAD Date, Start, End, Staff,True_Start,True_EndResident OrderLogTemp;Drop Table OrderLogTemp;EventLog:LOAD * INLINE [Time,Comment07:00,Start shift 108:00,Start shift 209:00,Start shift 3 and End of shift 110:00,Start shift 4 and End of shift 211:00,End of shift 3 and End of shift 312:00,End of Shift 4 and End of shift 4];IntervalLog:IntervalMatch(Time) load Start,End Resident OrderLog;
I Plan to update Time Load to it looks at Interval, (as below)
IntervalMatch(Time) load True_Start_Time,True_End_Time Resident OrderLog;
I cant get that faras the load fails