Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
wjalford
Level III

dt5 << Select where (:LOGIN_DATE >= informat(startDT2,"mm/dd/yyyy")); does not work

I have other scripts where I am using a calendar date box to get a date range to select rows from a table where >= the date range and it works.  I have one script where it will not work.

 

I am storing the date in a varaible startDT2.  The format matches what is in the table after I split the table.  Below is the code.

 

TempstartDT = start << get; // This gets the date from the Calendar/Date Box.
CharDate3= Uppercase(Format(TempstartDT, "mm/dd/yyyy"));
startDT2 = CharDate3;
dt5 << Select where (:LOGIN_DATE >= informat(startDT2,"mm/dd/yyyy"));

 

When I run the code in debugger I can see that startDT2 is 01/29/2019 when I select that date.  The data has rows where the LOGIN_DATe colulumn has 01/29/2019.  It ends up ignoring the select where and bringing in all of the data.

 

Does anyone know why the Select Where is not working?

 

 

8 REPLIES 8
Highlighted

Re: dt5 << Select where (:LOGIN_DATE >= informat(startDT2,"mm/dd/yyyy")); does n

The calendar date box captures a numeric value that represents the number of seconds since midnight, January 1, 1904.

 

The comparisn you want to make should not use a character string as produced by the formatting functions.

Learn it once, use it forever!
Highlighted
pmroz
Super User

Re: dt5 << Select where (:LOGIN_DATE >= informat(startDT2,"mm/dd/yyyy")); does n

What's the format of the LOGIN_DATE column?  If it's numeric (i.e. date) then you don't need to convert to character and then back to numeric date.

TempstartDT = start << get; // This gets the date from the Calendar/Date Box.
dt5 << Select where( :LOGIN_DATE >= tempstartdt);

 

Highlighted
wjalford
Level III

Re: dt5 << Select where (:LOGIN_DATE >= informat(startDT2,"mm/dd/yyyy")); does n

TempstartDT is in seconds. The CharDate3 converts it to mm/dd/yyyy. The column LOGIN_DATE is numeric with the format of m/d/y. The above suggestion does not work.
Highlighted
pmroz
Super User

Re: dt5 << Select where (:LOGIN_DATE >= informat(startDT2,"mm/dd/yyyy")); does n

All dates are in seconds, but you can choose how you want them displayed (i.e. m/d/y).  Can you post a small sample of your data?

Highlighted
wjalford
Level III

Re: dt5 << Select where (:LOGIN_DATE >= informat(startDT2,"mm/dd/yyyy")); does n

PRODUCT_CODE      LOT_NUMBER   LOGIN_DATE       VALUE_1

11111                       12345-01    12/19/2018     0.0028 

 

See attached file.

Highlighted
pmroz
Super User

Re: dt5 << Select where (:LOGIN_DATE >= informat(startDT2,"mm/dd/yyyy")); does n

This code works.  I entered a date prior to 12/19/2018 and both rows were highlighted.

dt5 = New Table( "Test", Add Rows( 2 ),
	New Column( "PRODUCT_CODE", Character( 6 ), "Nominal",
		Set Values( {"11111", "222"} ), Set Display Width( 103 )
	),
	New Column( "LOT_NUMBER", Character, "Nominal",
		Set Values( {"11111-01", "222-01"} ), Set Display Width( 88 )
	),
	New Column( "LOGIN_DATE", Numeric, "Continuous",
		Format( "m/d/y", 12 ), Input Format( "m/d/y" ),
		Set Values( [3628022400, 3631564800] ),
		Set Display Width( 82 )
	),
	New Column( "Value", Numeric, "Nominal", Format( "Best", 12 ),
		Set Values( [0.0028, 0.001] ), Set Display Width( 91 )
	)
);
nw = new window("test", modal(),
	start = number edit box(),
	start << Set Format( Format( "m/d/y", 12 ) );
);
TempstartDT = start << get; // This gets the date from the Calendar/Date Box.
dt5 << Select where( :LOGIN_DATE >= tempstartdt);

Maybe put some show() statements into your code to see what it's doing?

Highlighted
wjalford
Level III

Re: dt5 << Select where (:LOGIN_DATE >= informat(startDT2,"mm/dd/yyyy")); does n

Thank you for your help.  I ended up needing to subset the data first then the select where worked.

Highlighted

Re: dt5 << Select where (:LOGIN_DATE >= informat(startDT2,"mm/dd/yyyy")); does n

What does Informat() return?

Learn it once, use it forever!
Article Labels

    There are no labels assigned to this post.