topic Re: Select range of rows around user input start date in Discussions

Select range of rows around user input start date

ben_T — Thu, 30 Nov 2017 21:04:27 GMT

I am working with a large data set spanning many years, which can be simplified to two columns named "ID" and "Test_date" . Date is formatted as 'm/d/y', and the data table is sorted chronologically. I would like to select all of the rows where the test date is within one year (before or after) of the Test_date specified by the user, and then delete all other rows from the data table.

Below is what I have tried so far. I have created the upper and lower date limits for a input ID, but am unable to select the dates within that range using select where(). Instead, I get the error "The argument to SelectWhere did not evaluate to true or false, it is [0]... ". Any help on this would be much appreciated.

dt << current data table();
dt << new column("T_YEAR", numeric, continuous, set each value(:Test_date / (365*86400)));

setpoint =  ;//user specified numeric value
set_row= dt << get rows where(:ID == setpoint);

set_date = dt:T_YEAR[set_row] ;
set_min = (set_date - 1);
set_max = (set_date + 1);


dt << select where(:T_YEAR >= set_min & :T_YEAR <= set_max )
   << invert row selection
   << delete rows;

Re: Select range of rows around user input start date

pmroz — Thu, 30 Nov 2017 20:55:56 GMT

Datetimes are in units of seconds, so you'll to do something like this:

set_min = (set_date - (1 * 365 * 24 * 3600));
set_max = (set_date + (1 * 365 * 24 * 3600));

Re: Select range of rows around user input start date

ben_T — Thu, 30 Nov 2017 21:04:37 GMT

Yes, that's essentially what I did by creating a new column "T_YEAR" with the date converted to years using Test_date/ (365*86400). I've tried both, and it works either way. The issue seems to occur when using set_min and set_max variables in select where() to find all rows between those values.

Re: Select range of rows around user input start date

Jeff_Perkinson — Thu, 30 Nov 2017 21:23:55 GMT

Lots of ways to do this.

You can use the In Years() function to return the JMP datetime value for a number of years, or the Date Increment() function to add or subtract a number of years.

Also, you could get the Year() of your set date, add 1 to that and use the Date MDY() function to give you the JMP date value.

set_min = (set_date - In Years(1));
set_max = (set_date + In Years(1));

//or

set_min = Date Increment(set_date, "Year", -1, "actual");
set_max = Date Increment(set_date, "Year", 1, "actual");

//or

set_min = Date MDY( Month( set_date ), Day( set_date ), Year( set_date ) - 1 );
set_max = Date MDY( Month( set_date ), Day( set_date ), Year( set_date ) + 1 );

Re: Select range of rows around user input start date

Jeff_Perkinson — Thu, 30 Nov 2017 21:35:16 GMT

@ben_T, it looks like your reply and mine overlapped.

This works for me.

dt=open("$SAMPLE_DATA\TechStock.jmp");

set_date=datemdy(12, 15, 2000);

set_min = (set_date - In Days(5));
set_max = (set_date + In Days(5));


dt << select where(:Date >= set_min & :Date <= set_max )
   << invert row selection
   << delete rows;

Note that I had to change to In Days() instead of years only because the sample data didn't have more than one year in it, but other than that this should work for you.