topic Re: Select Where on Dates in Discussions

Select Where on Dates

KST-CPT — Mon, 26 Mar 2018 13:59:44 GMT

I am trying to create a subset of a data set. I want to select the dates outside of my start and end dates for analysis, then exclude such dates. Not sure why this is not working, seems easy. I think it is number format issues?

I have StartDate and EndDate as dates from CalendarBox()

:Date is a column with numeric dates in format m/d/y.

I have tried this to no success:

BegTime = dt<< Subset(All rows, Selected columns only( 0 ) );
BegTime << Select Where(:Date < Informat(StartDate) |:Date > Informat(EndDate))<<Exclude;));

To check the columns I tried this (below) but did not get a result. Just ".

BegTime <<new column("blah", numeric, formula(
num(:Date) - num(StartDate);

Re: Select Where on Dates

txnelson — Sun, 25 Mar 2018 04:50:49 GMT

I am assuming that you are dealing with date data that are JMP date values(numeric values, the number of seconds since Jan 1, 1904). Given that, there is no need to convert them to some value other than a numeric value. Therefore the below should work

BegTime = dt<< Subset(All rows, Selected columns only( 0 ) );

BegTime << Select Where(:Date < StartDate |:Date > EndDate)<<Exclude;

Re: Select Where on Dates

KST-CPT — Mon, 26 Mar 2018 14:00:52 GMT

Yes, that isn't working. Is it possible that my :Date values are not the same as my JMP calendar box() values?

This is how I cam calculating :Date

dt<< new column ("Date", numeric, continuous, format("m/d/y"), formula(
Date MDY(
Num( Substr( :TimeStamp, 1, 2 ) ),
Num( Substr( :TimeStamp, 4, 2 ) ),
Num( Substr( :TimeStamp, 7, 4 ) ) )
));

:TimeStamp are characters in the format 01:28:2018::02:14:08:099

StartDate and EndDate are from this:

new window("Start Date of Data",<<Modal, StartDate = Calendar Box());
Show( Short Date( StartDate << Get Date ) );
new window("End Date of Data",<<Modal, EndDate = Calendar Box());
Show( Short Date( EndDate << Get Date ) );

Re: Select Where on Dates

KST-CPT — Sun, 25 Mar 2018 05:40:25 GMT

:Date is definetly a value in seconds from 01JAN1904. If I remove the "m/d/y" formatting it is a similar number to the CalendarBox() numbers.

I can't even subtract these numbers from each other though. very confused?

Re: Select Where on Dates

txnelson — Sun, 25 Mar 2018 06:20:51 GMT

I think your issue is that you are thinking that StartDate and EndDate are numeric dates. The way you have constructed your code, StartDate and EndDate are pointers to objects. You need to extract the date values from the objects and place them into variables of your choosing. Also, your use a Modal window will not let any processing run, such as the setting of the range of the choices for the dates to choose from. So I have also removed that restriction. So if you follow the logic of the script below, you will see how to handle the processing control without using Modal windows.

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA\Time Series\Raleigh Temps.jmp" );
dt << clear row states;
startNW = New Window( "Start Date of Data",
	StartDate = Calendar Box(),
	Button Box( "OK",
		startNW << close window;
		runEndDate;
	)
	
);
StartDate << Max Date( Col Max( :name( "Month/Year" ) ) ) << Min Date( Col Min( :Name( "Month/Year" ) ) );


StartDate << Set Function( Function( {this, date}, theStartDate = StartDate << Get Date ) );

runEndDate = Expr(
	endNW = New Window( "End Date of Data",
		EndDate = Calendar Box(),
		Button Box( "OK",
			endNW << close window;
			runSelect;
		)
	);
	endDate << Max Date( Col Max( :name( "Month/Year" ) ) ) << Min Date( Col Min( :Name( "Month/Year" ) ) );
	EndDate << Set Function( Function( {this, date}, theEndDate = endDate << Get Date ) );
);

runSelect = Expr(
	dt << select where( :Name( "Month/Year" ) < theStartDate | :Name( "Month/Year" ) > theEndDate )
);