Turn on suggestions

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

Showing results for

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

Topic Options

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

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

Mar 13, 2019 2:27 PM
(247 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

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

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

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

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