Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- JMP User Community
- :
- Discussions
- :
- Need Help Assigning Column Based on Variable Times

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Mar 13, 2019 2:27 PM
(4034 views)

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

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Created:
Mar 13, 2019 9:09 PM
| Last Modified: Mar 14, 2019 3:45 AM
(4013 views)
| Posted in reply to message from OllieGRM 03-13-2019

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

5 REPLIES 5

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Created:
Mar 13, 2019 9:09 PM
| Last Modified: Mar 14, 2019 3:45 AM
(4014 views)
| Posted in reply to message from OllieGRM 03-13-2019

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
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Need Help Assigning Column Based on Variable Times

Thank you! That worked perfectly.

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Need Help Assigning Column Based on Variable Times

Created:
Aug 19, 2019 10:45 AM
| Last Modified: Aug 20, 2019 4:48 AM
(2491 views)
| Posted in reply to message from txnelson 03-14-2019

Hi Jim,

I am trying to create a new column for a 3-turn reverse rotating schedule. D crew no longer exists, only A, B, and C remain.

The crews rotate shifts in reverse order going from midnights-->afternoons-->days-->midnights every 7 days. So a full cycle completes every 21 days.

On 7/1/19, A crew was on midnights, B crew was on days, and C crew was on afternoons.

I have tried modifying your previous formula as shown below:

I deleted the D crew schedule and associated if statements, replaced the A,B,C crew schedules with the new schedules, tweaked "offset" to check the days since the last beginning of the now 21 day cycle, inputted 7/1/19 as the start date, and changed "shift" to start at position +2 from the Hour(targetTime). This took some trialing and erroring.

Can you explain why changing the shift to start at position +2 from the Hour(targetTime) allowed this formula to work versus starting at position +1 as was done for the original 4crew schedule? I'm trying to better understand this so I can more effectively implement future changes.

Thanks,

Greg

```
If( Row() == 1,
groupAschedule = "MMMMMMMAAAAAAADDDDDDD";
groupBschedule = "DDDDDDDMMMMMMMAAAAAAA";
groupCschedule = "AAAAAAADDDDDDDMMMMMMM";
hourSchedule = "MMMMMMMMDDDDDDDDAAAAAAAA";
startingDate = Informat( "7/1/2019", "M/D/Y" );
);
targetTime = :LadleOpenedTime;
If( Hour( targetTime ) == 23,
targetTime = targetTime + In Hours( 1 )
);
shift = Substr( hourSchedule, Hour( targetTime ) + 2, 1 );
offset = Mod( Date Difference( startingDate, targetTime, "Day" ), 21 ) + 1;
If(
Substr( groupASchedule, offset, 1 ) == shift, theCrew = "A",
Substr( groupBSchedule, offset, 1 ) == shift, theCrew = "B",
Substr( groupCSchedule, offset, 1 ) == shift, theCrew = "C"
);
thecrew;
```

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Need Help Assigning Column Based on Variable Times

Without seeing your :LadleOpenTime data, I can not be positive in my statement, but it appears that your LadleOpenTime values are not aligned with your hourSchedule.

Jim

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Need Help Assigning Column Based on Variable Times

Could it be due to the fact that the shifts are offset an hour? ie. 11-7, 7-3, 3-11, versus 12-8, 8-4, 4-12.

I changed the hourSchedule to "MMMMMMMDDDDDDDDAAAAAAAAM" and reverted the shift to start at position +1 and this worked all the same.

Thanks for taking the time to reply,

Greg

Article Labels

There are no labels assigned to this post.