topic Hide and Exclude dates column faster in Discussions

Hide and Exclude dates column faster

itzikd — Fri, 09 Jun 2023 19:48:11 GMT

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

Re: Hide and Exclude dates column faster

Jeff_Perkinson — Mon, 31 May 2021 12:55:05 GMT

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

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.

Re: Hide and Exclude dates column faster

itzikd — Mon, 31 May 2021 13:40:40 GMT

this did fix the issue, it now runs in 0.3 seconds thanks!

Re: Hide and Exclude dates column faster

Mark_Bailey — Tue, 01 Jun 2021 14:29:23 GMT

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