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

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

Newbie2Jumpie — Fri, 28 Jun 2019 04:55:16 GMT

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

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

martindemel — Fri, 28 Jun 2019 07:10:18 GMT

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 :)

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

txnelson — Fri, 28 Jun 2019 08:34:58 GMT

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 ) );

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

ian_jmp — Fri, 28 Jun 2019 08:44:09 GMT

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");