cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
Herrera5238
Level III

Select Where Duration Is Greater Than

Hello,

 

I'm trying to select rows where duration is greater than 1 sec. When I try to do this rows that show a duration of 1 sec are getting selected as well. What am I missing? Sample data table attached if needed.

 

Herrera5238_0-1580065820006.png

 

 

Details of what I'm trying to accomplish

We have data that is recorded every second. I was trying to check if there were any gaps or periods that were not recorded.

Currently trying to complete this check for an entire year, over 25 million rows. Final table would have a start date/time, end date/time column and a column that says the duration of time that there was no data for.

 

Steps taken

  1. Started with my column of Date&Time.
  2. Created a second column with formula Lag(:Date&Time, -1) and called it EndDate&Time
  3. Created a third column that took difference from EndDate&Time and Date&Time column and named it TimeDiff
  4. Attempted to select where TimeDiff is greater than 0:00:01

-Row that have a value displayed of 0:00:01 are also being selected.

 

1 ACCEPTED SOLUTION

Accepted Solutions
ron_horne
Super User (Alumni)

Re: Select Where Duration Is Greater Than

Hi @Herrera5238 

there are some issues with your formula. better to use the designated Duration formula which allows defining the units explicitly.

please see the attached file and open the column formulas.

 

View solution in original post

4 REPLIES 4
txnelson
Super User

Re: Select Where Duration Is Greater Than

The issue is that your display format is not displaying any decimal values for the duration.  Therefore, your value for TimeDiff for Row 1 is displayed as

     0:00:01

But if you specify in the format definition for the column to display 2 decimal points, you would see that Row 1's value is actually

     0:00:01.07

Which when you specify that you want to select all values greater than 1 second, 1.07 seconds is greater than 1 second, so it is selected.

Jim
Herrera5238
Level III

Re: Select Where Duration Is Greater Than

txnelson,

 

This helped me realize why the other rows were getting selected with my statement of select where TimeDiff >= 0:00:01. 

What I was really after was selecting TimeDiff >= 0:00:01.5. Had I had my display out to two decimal places I would have probably realized this sooner. 

 

Thank you!

ron_horne
Super User (Alumni)

Re: Select Where Duration Is Greater Than

Hi @Herrera5238 

there are some issues with your formula. better to use the designated Duration formula which allows defining the units explicitly.

please see the attached file and open the column formulas.

 

Herrera5238
Level III

Re: Select Where Duration Is Greater Than

Ron,

 

I did come across this formula before posting but I was still running into issues when using select where.

However I tried it today with a blank slate and it worked! I must have clearly done something wrong when I first tried Date Difference().

 

I'm selecting this as solution because you've included the other columns that I was looking to get on my end result.

 

I appreciate you, txNelson and the JMP community you guys are awesome!

 

Thank You