cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
wjalford
Level III

Need help with select where date.

I am creating a subset where I am using Select Where :Login Date == 01/18/2019;  I cannot get it to work.  I also tried opening the subset and running the query builder.  I can add all of the fields and then filter by login date.  The login dates show in the filter.  If I choose one of the dates and hit update, no records show.  Below is my code and I have attached the screen shot of the query builder.  Any help would be appreciated.

 

Regards,

 

Wayne

 

Capture-6.PNG

18 REPLIES 18
wjalford
Level III

Re: Need help with select where date.

Jim,

 

I agree.  Attached is a sample table.

 

Regards,

 

Wayne

wjalford
Level III

Re: Need help with select where date.

Jim,

 

That is the excel that I am importing.  After importing I get the following table.  See attached.

 

Wayne

wjalford
Level III

Re: Need help with select where date.

Jim,

 

The sample JMP table :Login Date is character format.  I believe the AlstartDT is numeric and that is why I am having the issue?  Do you believe this is the issue?  If so, what do you recommend?

 

Regards,

 

Wayne

txnelson
Super User

Re: Need help with select where date.

The issue is that you have :login Date as a character column, and it contains the time data along with the date data.  So when you compare it to ALstartDT, it is having issues.  I suggest that you uncomment the line where you attempted to change the :Login Date to a numeric with a format of "m/d/y".  It should slightly changed to look like

dt6:Login Date << data type(numeric) << modeling type(continuous)<< format( "m/d/y");

and then change your select where lines to

dt6 << Select where ((Contains(MyList, :Site ID)) & :Analysis == "G4");
dt6 << Select where (:Login Date >= informat(ALstartDT,"mm/dd/yyyy"),current selection("extend"));
Jim
wjalford
Level III

Re: Need help with select where date.

Jim,

 

No errors, but it does not subset by the ALstartDT date pulled in from the date textbox.  My file contains rows with login dates of 1/2/2019 through 1/24/2019.  My ALstartDT is 01/18/2019.

 

Regards,

 

Wayne

wjalford
Level III

Re: Need help with select where date.

Jim, 

I noticed in the debug tool that it states that ALstartDT is a string not a number.  Hopefully this helps.

 

Regards,

 

Wayne

wjalford
Level III

Re: Need help with select where date.

Jim,

I finally figured it out. I took your solution then got rid of the extend and added it back to my original select where. For some reason the extend did not work.
Thank you for taking all of the time helping me work this out. Very much appreciated. Without all of the experts on this forum, folks learning like myself would not be able to complete their projects.

Regards,

Wayne

Re: Need help with select where date.

Note that you can use literal date values (constants) directly without the need to convert between character strings and numeric values. The form is DDMmmYYYY:HH:MM:SS.S. (You only use as much of this form as necessary, omitting from the right side.) So, for example, I could directly enter the correct numeric value for 8 AM today as:

 

29Jan2019:08:00

The script editor acknowledges this value as a number with the color coding if you have the feature enabled.

 

Jeff_Perkinson
Community Manager Community Manager

Re: Need help with select where date.

To expand on @Mark_Bailey's response here is an example showing the date constant in use.

 

 

dt = open("$SAMPLE_DATA\TechStock.jmp");
dt << select where(Date <= 05Jan2001);

 

For more tips on dates see Using dates, times, datetimes and durations in JMP.

-Jeff