topic Re: Need help with select where date. in Discussions

Need help with select where date.

wjalford — Sat, 02 Feb 2019 03:42:34 GMT

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

Re: Need help with select where date.

txnelson — Tue, 29 Jan 2019 00:59:08 GMT

JMP Date values are numeric values. If you wanted to compare a value in "Login Date" to a date value, you either need to convert the value of "Login Date" to a character string and compre it to "01/07/2018", or keep it as a numeric and convert "01/07/2018" to a date time numeric value. The script below does the selection using the former methodolgy

Column( "Login Date" ) << data type( numeric ) << Format( "m/d/y" );
MyList = {"HCDS", "HCDS;LENNY", "HCDS; WACKER", "HCDS; GOLDEN SAMPLE", "HCDS; ALJ", "HCDS; LENNY C",
"HCDS; WACKER; GOLDEN SAMPLE", "HCDS; WACKER; PRE-MOVE"};
dt6 << Select where( (Contains( MyList, :Site ID )) & :Analysis == "G4" );
dt6 << Select where( format(Column( "Login Date" )[Row()], "M/D/Y" ) == "01/07/2018", current selection( "extend" ) );
dt7 = dt6 << Subset( Output Table( "Aloha Lenny Assay Subset" ), Selected Rows( 1 ), Selected Columns( 0 ) );
dt7 << select where( Is Missing( :Label ID ) ) << delete rows;

selrows = dt7 << get rows where( :Formatted Entry == "ND" );
dt7:Formatted Entry[selRows] = "0";
dt7:Formatted Entry << data type( Numeric );

Re: Need help with select where date.

wjalford — Sat, 02 Feb 2019 03:32:14 GMT

Jim,

I tried your suggestion and still the data did not filter. Below is what I used.

Regards,

Wayne

Re: Need help with select where date.

txnelson — Tue, 29 Jan 2019 03:09:37 GMT

Here is a Select Where example, in the same structure as your noted Select Where. It works. So I believe you need to possibly use the JSL Debugger to step through your code and find out where the issue(s) is/are.

names default to here(1);
dt= open("$SAMPLE_DATA/Aircraft Incidents.jmp");
MyList = {"Accident", "Incident" };
dt << Select where( (Contains( MyList, :Investigation Type) & :Location == "Eden, UT"));
dt << Select where( format(Column( "Event Date" )[Row()], "M/D/Y" ) == "01/05/2001", current selection("extend"));

I checked out the code back to JMP 11. The "current selection("extend")", was added in JMP 12, so the JSL does not work properly in JMP 11, but it does work after that release.

Re: Need help with select where date.

Mark_Bailey — Tue, 29 Jan 2019 12:20:40 GMT

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.

Re: Need help with select where date.

Jeff_Perkinson — Tue, 29 Jan 2019 15:56:14 GMT

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.

Re: Need help with select where date.

wjalford — Sat, 02 Feb 2019 03:28:20 GMT

Jim,

I am using 14.1. I have tried so many different ways to format my dates and have not figured out the right format. Below is more of my code. I am passing a variable from a date box. My excel import Login Date looks like

1/2/2019 9:11

Please take a look if I am passing the ALstartDT correctly and any recommendation on how to properly format the date to get the select to work.

Thank you,

Wayne

Clear Log();

Names Default to Here (1);

runScript = Expr(
TempstartDT = start << get;
TempendDT = end << get;
ALstartDT = Uppercase(Format(TempstartDT, "mm/dd/yyyy"));
ALendDT = Uppercase(Format(TempendDT, "mm/dd/yyyy"));

);

// Written by Vince Faller
// Makes a calendar come up on a numeditbox (defaults to now)
// Note, this only works on JMP 11 or above
dateBox = Function(
{timething = Today()},
{newbox},
newbox = Eval(
Substitute(
Name Expr(
Number Edit Box(
timething, // Eval(Sub()) because of JMP quirk
10, // Just need this because JMP doesn't default a width
<<Set function(
Function( {self},
{},
If( Is Missing( self << get ),
self << Set( Eval( DUMMY ) ) // Sets the date to the timething argument
)
)
),
<< Set Format(Format( "m/d/y h:m:s", 23, 0 ) ); // Makes datetime format for the box (gives calendar)
)
),
Expr( DUMMY ), timething
)
);
newbox;
);

nw =
New Window( "SQL Data Table Extraction",
LineUpBox(NCol(2),
TextBox("Start DateTime"), start = dateBox(Today()- InDays(12)),
TextBox("End DateTime"), end = dateBox(),
ButtonBox("Cancel", ButtonPressed = "Cancel"), ButtonBox("Get Data", ButtonPressed = "Get Data") //stores users button press input into variable

));

wait (15);
//If statements to take action based on which button was pressed on the date prompt
If ( ButtonPressed == "Cancel", nw << Close Window);
If ( ButtonPressed == "Cancel", Stop());
if ( ButtonPressed == "Get Data", runScript);
If ( ButtonPressed == "Get Data", runScript2);
If ( ButtonPressed == "Get Data", nw << Close Window);

Caption("Query is running");
wait(0);
Names Default to Here (1);

Use for all sheets( 1 ),
Concatenate Worksheets( 0 ),
Create Concatenation Column( 0 ),
Worksheet Settings(
1,
Has Column Headers( 1 ),
Number of Rows in Headers( 1 ),
Headers Start on Row( 1 ),
Data Starts on Row( 2 ),
Data Starts on Column( 1 ),
Data Ends on Row( 0 ),
Data Ends on Column( 0 ),
Replicated Spanned Rows( 1 ),
Replicated Spanned Headers( 0 ),
Suppress Hidden Rows( 1 ),
Suppress Hidden Columns( 1 ),
Suppress Empty Columns( 1 ),
Treat as Hierarchy( 0 ),
Multiple Series Stack( 0 ),
Import Cell Colors( 0 ),
Limit Column Detect( 0 ),
Column Separator String( "-" )
);

);
//Column ("Login Date") << data type (numeric) << format("m/d/y");

dt6 << Select where (:Login Date >= ALstartDT,current selection("extend"));

dt7 << select where(Is Missing (:Label ID)) << delete rows;

selrows=dt7 << get rows where (:Formatted Entry == "ND");
dt7:Formatted Entry[selRows]="0";
dt7:Formatted Entry << data type(Numeric);

Re: Need help with select where date.

txnelson — Tue, 29 Jan 2019 21:17:23 GMT

Because you have set your variable "ALstartDT" to a string

ALstartDT = Uppercase( Format( TempstartDT, "mm/dd/yyyy" ) );

(The Format() function returns a string value)

You then need to convert your ":Login Date" to a string also....the ":Login Date" I am assuming is a JMP Date Column.

dt6 << Select where( Format(:Login Date[row()], "mm/dd/yyyy") >= ALstartDT, current selection( "extend" ) );

Re: Need help with select where date.

wjalford — Wed, 30 Jan 2019 13:04:04 GMT

Jim,

When I try to convert :Login Date to a string, I recieve the error that argument should be numeric{1} in access or evaluation of 'Format', Format/*###*/:Login Date [Row()], "mm/dd/yyyy")

Regards,

Wayne

Re: Need help with select where date.

txnelson — Wed, 30 Jan 2019 15:34:30 GMT

Oops, my error. The [Row()] is the problem. Use

dt << select where( Format( :Login Date, "mm/dd/yyyy" ) <= ALstartDT );

Re: Need help with select where date.

wjalford — Wed, 30 Jan 2019 15:42:00 GMT

Jim,

I still get argument should be numeric{1} in access or evaluation of 'Format' , Format/*###*/(:Login Date, "mm/dd/yyyy")

Re: Need help with select where date.

txnelson — Wed, 30 Jan 2019 15:55:18 GMT

I am pretty sure the issue is a mismatch in whether or not the column is character vs. the comparison variable is character. Can you attach a sample data table?

Re: Need help with select where date.

wjalford — Wed, 30 Jan 2019 15:58:49 GMT

Jim,

I agree. Attached is a sample table.

Regards,

Wayne

Re: Need help with select where date.

wjalford — Wed, 30 Jan 2019 16:02:12 GMT

Jim,

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

Wayne

Re: Need help with select where date.

wjalford — Wed, 30 Jan 2019 16:05:11 GMT

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

Re: Need help with select where date.

txnelson — Wed, 30 Jan 2019 16:41:30 GMT

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"));

Re: Need help with select where date.

wjalford — Wed, 30 Jan 2019 17:29:36 GMT

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

Re: Need help with select where date.

wjalford — Wed, 30 Jan 2019 17:42:11 GMT

Jim,

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

Regards,

Wayne

Re: Need help with select where date.

wjalford — Wed, 30 Jan 2019 18:10:04 GMT

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