BookmarkSubscribe
Choose Language Hide Translation Bar
Newbie2Jumpie
Contributor

JSL: Extract day of week from datetime field (scripting)

Hi

 

I have a field START_TIME (datetime format), e.g.

15.06.2019 6:05

16.06.2019 8:12

17.06.2019 7:01 etc.

I would like to keep all days that are Saturdays and Sundays.

 

I imagine, by extracting I would receive a value like

START_TIME         SAT_SUN

15.06.2019 6:05             7

16.06.2019 8:12             1

17.06.2019 7:01             2

Then I just use keep 1s and 7s, right.

 

How would I do that with JSL script?

I tried approaches like

dt << New Column ("SAT_SUN",numeric,format(day of week(:START_TIME),"datetime")) ;

that don't seem to work.

Any idea?

Cheers!

Newbie

0 Kudos
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
txnelson
Super User

Re: JSL: Extract day of week from datetime field (scripting)

There is no need to create a new column to do what you want.  Here is a simple script that does what you asked for:

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Aircraft Incidents.jmp" );

dt << select where( Day Of Week( :Event Date ) == 1 | Day Of Week( :Event Date ) == 7 );

dtsub = dt << subset( selected rows( 1 ), selected columns( 0 ) );
Jim
3 REPLIES 3

Re: JSL: Extract day of week from datetime field (scripting)

Just qick idea (as I have to leave in a minute): If you want to keep all values you can just use the filter command and exclude all the others not matching SAT/SUN.
If you really want to remove the rows not equal to SAT/SUN then use SELECT WHERE and then delete rows.MAy someone can write a demo script :)
0 Kudos
ian_jmp
Staff

Re: JSL: Extract day of week from datetime field (scripting)

If needed, use 'Help > Scripting Index' to find more details of how the commands work;

NamesDefaultToHere(1);

// Make a table of random dates
n = 50;
startDate = Date DMY( 01, 1, 2016 );
endDate = Date DMY( 12, 10, 2016 );
dates = Sort Ascending( J( n, 1, Random Integer(startDate, endDate) ) );
dt = New Table( "Dates",
			New Column( "Random dates", Numeric, "Continuous", Format( "ddMonyyyy h:m", 35 ), Set Values( dates )),
		);

// Add a formula for the day of the week
nc = dt << New Column( "Day of the week",
						Numeric,
						"Continuous",
						Format( "Best", 12 ),
						Formula( Day Of Week( :Random dates ) ),
						Value Labels(
							{1 = "Sunday", 2 = "Monday", 3 = "Tuesday", 4 = "Wednesday", 5 = "Thursday",
							6 = "Friday", 7 = "Saturday"}
						),
						Use Value Labels( 1 ),
					);

// Make a subset table
r = dt << getRowsWhere(:Day of the week == 7 | :Day of the week == 1);
dt2 = dt << Subset(rows(r), LinkToOriginalDataTable(1));
dt2 << setName("Weekend Dates");

 

Highlighted
txnelson
Super User

Re: JSL: Extract day of week from datetime field (scripting)

There is no need to create a new column to do what you want.  Here is a simple script that does what you asked for:

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Aircraft Incidents.jmp" );

dt << select where( Day Of Week( :Event Date ) == 1 | Day Of Week( :Event Date ) == 7 );

dtsub = dt << subset( selected rows( 1 ), selected columns( 0 ) );
Jim