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
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.
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.
Floor(((if(End<Start,End+1,End))*24)) as True_End,
Floor(((Date+Start)-Date)*24) as True_Start
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
Drop Table OrderLogTemp;
LOAD * INLINE [
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
IntervalMatch(Time) load Start,End