BookmarkSubscribeRSS Feed
Choose Language Hide Translation Bar
wjalford

Contributor

Joined:

Jan 16, 2019

Re: Need help with select where date.

Jim,

 

I agree.  Attached is a sample table.

 

Regards,

 

Wayne

wjalford

Contributor

Joined:

Jan 16, 2019

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

Contributor

Joined:

Jan 16, 2019

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

Joined:

Jun 22, 2012

Solution

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
Highlighted
wjalford

Contributor

Joined:

Jan 16, 2019

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

Contributor

Joined:

Jan 16, 2019

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

Contributor

Joined:

Jan 16, 2019

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
markbailey

Staff

Joined:

Jun 23, 2011

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

Joined:

Jun 23, 2011

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