BookmarkSubscribeRSS Feed
Choose Language Hide Translation Bar
OllieGRM

New Contributor

Joined:

Mar 13, 2019

Need Help Assigning Column Based on Variable Times

Hello,

I am attempting to create a new column in my data table 'Crew' based on an existing datetime column, LadleOpenTime (a small subset of ladleopentimes is shown below).

 

The times for the Crews (A,B,C, or D swing shifts) follow a fixed schedule shown by the excel table below:

The schedule is 7 days on, two days off, 7 days on, two days off, 7 days on 3 days off, then repeat for each crew.

Midnights (11:00PM-7:00AM), Days (7:00AM-3:00PM), Afternoons (3:00PM-11:00PM).

 

Is there any way to code this into jsl?

Appreciate any and all feedback

turn schedule.JPGLadleOpenTime.JPG

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson

Super User

Joined:

Jun 22, 2012

Solution

Re: Need Help Assigning Column Based on Variable Times

I believe the following will get you what you want.  The only thing you need to do, is to set a date in the past which is a Sunday, where Crew C is working Midnights, Crew A is working Days, Crew D is working Afternoons, and Crew B is Off.  This date starts the cycle of 28 days for your pattern.  The formula determines the number of days from the latest beginning of the cycle, determines the Shift, and then finds which Crew was working that Shift.  I don't have any actual data, but I believe the methodology will work.  Try it with some of your real data.  Remember, my StartingDate value is not correct.  You need to change it as described above. Just create your new column called "Crew" and paste this formula into it.  If you have any problems, please respond and provide a correct StartingDate, and attach a sample data table that has your LabelOpenTime column.

If( Row() == 1,
	groupAschedule = "DOOAAAAAAAOOMMMMMMMOOODDDDDD";
	groupBschedule = "ODDDDDDDOOAAAAAAAOOMMMMMMMOO";
	groupCschedule = "MMMMMOOODDDDDDDOOAAAAAAAOOMM";
	groupDschedule = "AAAOOMMMMMMMOOODDDDDDDOOAAAA";

	hourSchedule = "MMMMMMMDDDDDDDDAAAAAAAA";

	startingDate = Informat( "12/31/2017", "M/D/Y" );
);


targetTime = :LadleOpenTime;
If( Hour( targettime ) == 23,
	targetTime = targetTime + In Hours( 1 )
);

shift = Substr( hourSchedule, Hour( targetTime ) + 1, 1 );

offset = Mod( Date Difference( startingDate, targetTime, "Day" ), 28 ) + 1;

If(
	Substr( groupASchedule, offset, 1 ) == shift, theCrew = "A",
	Substr( groupBSchedule, offset, 1 ) == shift, theCrew = "B",
	Substr( groupCSchedule, offset, 1 ) == shift, theCrew = "C",
	Substr( groupDSchedule, offset, 1 ) == shift, theCrew = "D"
);
thecrew;

 

Jim
2 REPLIES 2
txnelson

Super User

Joined:

Jun 22, 2012

Solution

Re: Need Help Assigning Column Based on Variable Times

I believe the following will get you what you want.  The only thing you need to do, is to set a date in the past which is a Sunday, where Crew C is working Midnights, Crew A is working Days, Crew D is working Afternoons, and Crew B is Off.  This date starts the cycle of 28 days for your pattern.  The formula determines the number of days from the latest beginning of the cycle, determines the Shift, and then finds which Crew was working that Shift.  I don't have any actual data, but I believe the methodology will work.  Try it with some of your real data.  Remember, my StartingDate value is not correct.  You need to change it as described above. Just create your new column called "Crew" and paste this formula into it.  If you have any problems, please respond and provide a correct StartingDate, and attach a sample data table that has your LabelOpenTime column.

If( Row() == 1,
	groupAschedule = "DOOAAAAAAAOOMMMMMMMOOODDDDDD";
	groupBschedule = "ODDDDDDDOOAAAAAAAOOMMMMMMMOO";
	groupCschedule = "MMMMMOOODDDDDDDOOAAAAAAAOOMM";
	groupDschedule = "AAAOOMMMMMMMOOODDDDDDDOOAAAA";

	hourSchedule = "MMMMMMMDDDDDDDDAAAAAAAA";

	startingDate = Informat( "12/31/2017", "M/D/Y" );
);


targetTime = :LadleOpenTime;
If( Hour( targettime ) == 23,
	targetTime = targetTime + In Hours( 1 )
);

shift = Substr( hourSchedule, Hour( targetTime ) + 1, 1 );

offset = Mod( Date Difference( startingDate, targetTime, "Day" ), 28 ) + 1;

If(
	Substr( groupASchedule, offset, 1 ) == shift, theCrew = "A",
	Substr( groupBSchedule, offset, 1 ) == shift, theCrew = "B",
	Substr( groupCSchedule, offset, 1 ) == shift, theCrew = "C",
	Substr( groupDSchedule, offset, 1 ) == shift, theCrew = "D"
);
thecrew;

 

Jim
Highlighted
OllieGRM

New Contributor

Joined:

Mar 13, 2019

Re: Need Help Assigning Column Based on Variable Times

Thank you! That worked perfectly.