BookmarkSubscribeRSS Feed
Choose Language Hide Translation Bar
NCSU_MTLRGST
New Contributor

Returning average values based on start and stop times from another table

I have a JMP query which currently returns batch start and stop times for a given process:

 

BatchStart TimeStop Time
11/1/2019 1:50 AM1/1/2019 4:50 AM
21/1/2019 6:50 AM1/1/2019 8:50 AM

 

I have a separate database which I can query that has sensor data:

 

TimestampSensor
1/1/2019 12:50 AM50
1/1/2019 1:50 AM90
1/1/2019 2:50 AM110
1/1/2019 3:50 AM100
1/1/2019 4:50 AM100
1/1/2019 5:50 AM50
1/1/2019 6:50 AM70
1/1/2019 7:50 AM90
1/1/2019 8:50 AM80
1/1/2019 9:50 AM80

 

  What I would like to do is calculate an average value from a separate table based on the start and stop times, and join that average value to my original table:

 

BatchStart TimeStop TimeAVG Sensor
11/1/2019 1:50 AM1/1/2019 4:50 AM100
21/1/2019 6:50 AM1/1/2019 8:50 AM80
0 Kudos
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
julian
Staff

Re: Returning average values based on start and stop times from another table

Hi @NCSU_MTLRGST,

There are a few ways to go about this, but if you don't want to do any scripting you can accomplish it completely using JMP Query Builder, under Tables > JMP Query Builder. Here's a quick video on how to do that (it's less complicated to see it than write out the steps). I've also attached all the tables I created in the process. 

I hope this helps!

@julian 

(view in My Videos)

3 REPLIES 3
Highlighted
julian
Staff

Re: Returning average values based on start and stop times from another table

Hi @NCSU_MTLRGST,

There are a few ways to go about this, but if you don't want to do any scripting you can accomplish it completely using JMP Query Builder, under Tables > JMP Query Builder. Here's a quick video on how to do that (it's less complicated to see it than write out the steps). I've also attached all the tables I created in the process. 

I hope this helps!

@julian 

(view in My Videos)

NCSU_MTLRGST
New Contributor

Re: Returning average values based on start and stop times from another table

Wow!  Thank you for the extremely thorough explanation and solution!!!

0 Kudos
julian
Staff

Re: Returning average values based on start and stop times from another table

You're welcome! It was a great question!
0 Kudos