BookmarkSubscribe
Choose Language Hide Translation Bar
wjalford
Contributor

Re: Need help with select where date.

Jim,

 

I agree.  Attached is a sample table.

 

Regards,

 

Wayne

0 Kudos
wjalford
Contributor

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

0 Kudos
wjalford
Contributor

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

0 Kudos
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
Contributor

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

0 Kudos
Highlighted
wjalford
Contributor

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

0 Kudos
wjalford
Contributor

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
0 Kudos

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.

 

Learn it once, use it forever!
Jeff_Perkinson
Community Manager Community Manager

Re: Need help with select where date.

To expand on @markbailey'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