389490_en-US

liliconcom

Hide and Exclude dates column faster

Hello,

I have a JMP table with 76K rows with a column named "sortDateColumn" that has dates in this format:

 

09/24/2020 11:29:25 AM

in my code I have this line that takes 4 seconds to run

 

 

minDate = "02/16/2021 12:23:09 AM";<BR />dt << Select where( Column( sortDateColumn )[] <= Informat( minDate, "MDYHMS" ) ) << Hide and Exclude();

is there a faster way to run this line? since doing this a few times slows my whole code down by like 1 minute

if i do on another row 

dt << Select Where( :"age" < 650 );

it takes less then 0.1 seconds but with the dates it takes a lot more

 

 

 

ScriptingRe: Hide and Exclude dates column faster

I do not know how much this change will speed up this step, but one of these versions might be worth a little improvement.

 

// call Informat() function once for constant time point
minDate = Informat( "16Feb2021 12:23:09 AM" );
Current Data Table() << Select Where( :column 1 <= minDate ) << Hide and Exclude();

// use literal value for constant time point
Current Data Table() << Select Where( :column 1 <= 16Feb2021:12:23:09 ) << Hide and Exclude();

Re: Hide and Exclude dates column fasterthis did fix the issue, it now runs in 0.3 seconds thanks!Re: Hide and Exclude dates column faster

There are a few errors with your code but I think the one that is slowing you down the most is that there is no informat "MDYHMS". That's causing many errors to be written to the log and that's what taking so long. Take a look in your log and see if I'm correct.

 

Try this instead:

 

 

minDate="02/16/2021 12:23:09 AM";
current data table() << Select where( :column 1 <= informat(minDate, "m/d/y h:m:s" )) << Hide and Exclude();

 

 

However, even that's more than you need. You can avoid the informat all together by using a datetime constant (you can read about them in Using dates, times, datetimes and durations in JMP).

 

minDate="16Feb2021 12:23:09 AM";
current data table() << Select where( :column 1 <= informat( minDate ) << Hide and Exclude();

Finally, you had a spurious [] after your Column() designation. I don't think that's useful here.