nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Need help with select where date.

wjalford

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

Accepted answers

txnelson

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

All comments

txnelson

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

wjalford

Jim,

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

Regards,

Wayne

txnelson

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.

Mark_Bailey

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

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.

wjalford

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