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
- :
- help with jsl to convert table column from datetim...

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

Apr 14, 2016 5:01 PM
(787 views)

Hi All,

I am looking for help to convert a table column from date time to work week, can you help me with a script to accomplish this conversion?

My data format is: 2016/03/21 8:59:27.921 PM and I'd like to convert this to calendar work week. I am using JMP 12.

Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions

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

Solution

Use the JMP function: Week of Year();

You will either have to create a new column with the above function as the formula for the column, or to replace the values within the same column the following script will do it: Assume that the target column is named......MyDate

Names Default to Here(1);

dt=current data table();

For Each Row(

MyDate=Week of Year(MyDate);

);

dt:MyDate << format("Best",5);

Jim

2 REPLIES

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

Use the JMP function: Week of Year();

You will either have to create a new column with the above function as the formula for the column, or to replace the values within the same column the following script will do it: Assume that the target column is named......MyDate

Names Default to Here(1);

dt=current data table();

For Each Row(

MyDate=Week of Year(MyDate);

);

dt:MyDate << format("Best",5);

Jim

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

Apr 15, 2016 6:48 AM
(676 views)

Thanks Jim you nailed it,

Here's the solution:

dt5 << New Column( "Work Week", Numeric, "Continuous", Formula( Week of Year(:StartTime,3) ) );

I used your suggestion to create a new column with the Week of Year formula. I added the argument for rule 3 which is, according to the JSL Syntax Reference, ISO Work Week:

From the Syntax Reference:

‒ |

‒ | With rule 2, the first Sunday begins with week 1, with previous days being week 0. |

‒ |