topic Select Where Duration Is Greater Than in Discussions

Select Where Duration Is Greater Than

Herrera5238 — Sun, 26 Jan 2020 19:10:45 GMT

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.

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

Started with my column of Date&Time.
Created a second column with formula Lag(:Date&Time, -1) and called it EndDate&Time
Created a third column that took difference from EndDate&Time and Date&Time column and named it TimeDiff
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.

Re: Select Where Duration Is Greater Than

txnelson — Sun, 26 Jan 2020 21:04:36 GMT

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.

Re: Select Where Duration Is Greater Than

ron_horne — Sun, 26 Jan 2020 21:12:26 GMT

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.

Re: Select Where Duration Is Greater Than

Herrera5238 — Tue, 28 Jan 2020 14:15:34 GMT

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!

Re: Select Where Duration Is Greater Than

Herrera5238 — Tue, 28 Jan 2020 14:24:11 GMT

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