Would intervalmatch work for what I am trying to do?

[#1]

Basically I have a data base that produces a time stamp every time a unit is produced in a factory. The time stamp is formatted as MM/DD/YYYY HH:MM:SS (24-hour time). What I am trying to do is create a drill down dimension as follows: Year->Month->DayNumber->Line->Shift. I have everything figured out except for how to derive which shift the timestamp would fall into. The data being loaded looks like:

Line	Timestamp	Ok Pieces Produced
Front right	11/30/2015 06:20:02	1
Rear Left	11/30/2015 13:03:18	1
Front Left	11/30/2015 17:03:43	0
Front Left	12/01/2015 06:53:02	1
Rear Right	12/01/2015 16:47:22	0

I currently have the Timestamp filed mapped to derive 'Year' 'Month' 'DayNumber'. The line field already has the info I need for the 'line' dimension of the drill down so all that I am missing is for to determine the shift. Basically I want the result of the above table to look like the below table once it is loaded:

Line	Timestamp	Ok Pieces Produced	Crew
Front right	11/30/2015 06:20:02	1	C-Crew
Rear Left	11/30/2015 13:03:18	1	A-Crew
Front Left	11/30/2015 17:03:43	0	B-Crew
Front Left	12/01/2015 06:53:02	1	A-Crew
Rear Right	12/01/2015 16:47:22	0	C-Crew

I created the following table to display the weekly schedule for the shifts:

Crew	Start Day	Start Time	End Time
C-Crew	Sunday	4:30:00 PM	6:29:59 AM
A-Crew	Monday	6:30:00 AM	4:29:59 PM
B-Crew	Monday	4:30:00 PM	6:29:59 AM
A-Crew	Tuesday	6:30:00 AM	4:29:59 PM
C-Crew	Tuesday	4:30:00 PM	6:29:59 AM
C-Crew	Wednesday	6:30:00 AM	4:29:59 PM
B-Crew	Wednesday	4:30:00 PM	6:29:59 AM
C-Crew	Thursday	6:30:00 AM	4:29:59 PM
A-Crew	Thursday	4:30:00 PM	6:29:59 AM
B-Crew	Friday	6:30:00 AM	4:29:59 PM

My question is, when I load the original database file, how do I add the field "Crew" in which crew is determined by comparing the timestamp to this schedule? I was able to use "if" statements to do something similar to this for another unit, but in that case the crew worked the same shift everyday so I could simply use the time interval. In this case I need to first analyze which day of the week the timestamp refers to, and then which time interval it would be contained by. I read a few documents on the interval match function, but they don't show how to add a field to the file being loaded, rather, they just show how to link two tables and I am not sure if that would work for creating a dimension. Any advice on the best method to script this would be greatly appreciated!

[marcus_sommer]

I think an intervalmatch should be possible if you extend your crew-shift-table to a date or you used only the time-part - time(frac(Timestamp)) - from yor production-table. But over a longer period of time the crew-shifts might change so that the date-extension will be necessary. Here you will find a good explanations for IntervalMatch‌.

- Marcus

[jonathandienst]

You will need to convert the time stamps in the production file to a week offset time (lets call it WeekTime), adjusted for 0 = Sunday in decimal days:

Production:

LOAD *,

  If(Offset + 1 >= 7, Offset - 6, Offset + 1) As WeekTime

;

LOAD *,

  Timestamp - WeekStart(Timestamp) As Offset

;

LOAD

  Line,

  Timestamp#(Timestamp, 'MM/dd/yyyy hh:mm:ss') As Timestamp,

  Pieces,

  ...

When you load the shifts, you will need to convert the start date to the same offsets, and compute and end date (which I have assumed to be the next day if the end time is before 12:00 noon) also in decimal days:

Shifts:

LOAD Crew,

  StartDay + Start As ShiftStart,

  EndDay + End As ShiftEnd

;

LOAD Crew,

  StartDay,

  If(End < 0.5, StartDay + 1, StartDay) As EndDay,

  Start,

  End

;

LOAD Crew,

  Dual([Start Day], Floor(Index('SunMonTueWedThuFriSat', Left([Start Day], 3))/3)) As StartDay,

  Time#([Start Time], 'h:mm:ss tt') As Start,

  Time#([End Time], 'h:mm:ss tt') As End

From Shifts....

Now you can use interval matching to link the crew to the production line:

Left Join (Production)

IntervalMatch(WeekTime)

LOAD ShiftStart, ShiftEnd

Resident Shifts;

If you want to bring the Crew into the production table (not strictly necessary - the model will work correctly without this step), and possibly remove the shifts table which will no longer be required:

Left Join (Production)

LOAD Crew, ShiftStart, ShiftEnd Resident Shifts;

DROP Fields ShiftStart, ShiftEnd From Production;

DROP Table Shifts;

I have attached a complete script which uses inline loads - these can easily be converted to file or database loads. I get this output:

The shifts table before interval matching:

The results table

[#4]

Thank you so much for the example script. This is exactly what I was looking for!