topic Re: Working between times in QlikView

Working between times

Anonymous — Wed, 20 Nov 2013 12:45:41 GMT

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

Re: Working between times

hic — Wed, 20 Nov 2013 13:38:35 GMT

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

Re: Working between times

Wed, 20 Nov 2013 14:11:34 GMT

Hi,

Check this attch file and let me know

Regards,

Ashutosh

Re: Working between times

Anonymous — Thu, 21 Nov 2013 09:06:22 GMT

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?

Re: Working between times

Thu, 21 Nov 2013 09:16:37 GMT

hi,

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

Thanks,

Ashutosh

Re: Working between times

Anonymous — Thu, 21 Nov 2013 09:20:31 GMT

Apols

Ashutosh

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

Regards

Phil

Re: Working between times

Thu, 21 Nov 2013 09:45:40 GMT

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

Re: Working between times

Anonymous — Thu, 21 Nov 2013 10:03:40 GMT

Ashutosh

Could you give me an example of what you mean?

Regards

Phil

Re: Working between times

Anonymous — Thu, 21 Nov 2013 15:15:13 GMT

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

Re: Working between times

Anonymous — Thu, 21 Nov 2013 15:19:57 GMT

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

Re: Working between times

Fri, 22 Nov 2013 04:41:26 GMT

Hi Phil,

Sorry for the late reply. I was little bit busy with my work. Can you please attach your sample file. I want to check once again.

Thanks,

Ashutosh