Returning average values based on start and stop times from another tableI have a JMP query which currently returns batch start and stop times for a given process:
| Batch | Start Time | Stop Time |
| 1 | 1/1/2019 1:50 AM | 1/1/2019 4:50 AM |
| 2 | 1/1/2019 6:50 AM | 1/1/2019 8:50 AM |
I have a separate database which I can query that has sensor data:
| Timestamp | Sensor |
| 1/1/2019 12:50 AM | 50 |
| 1/1/2019 1:50 AM | 90 |
| 1/1/2019 2:50 AM | 110 |
| 1/1/2019 3:50 AM | 100 |
| 1/1/2019 4:50 AM | 100 |
| 1/1/2019 5:50 AM | 50 |
| 1/1/2019 6:50 AM | 70 |
| 1/1/2019 7:50 AM | 90 |
| 1/1/2019 8:50 AM | 80 |
| 1/1/2019 9:50 AM | 80 |
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:
| Batch | Start Time | Stop Time | AVG Sensor |
| 1 | 1/1/2019 1:50 AM | 1/1/2019 4:50 AM | 100 |
| 2 | 1/1/2019 6:50 AM | 1/1/2019 8:50 AM | 80 |
Re: Returning average values based on start and stop times from another tableHi @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
LITHIUM.OoyalaPlayer.addVideo('https:\/\/player.ooyala.com\/static\/v4\/production\/', 'lia-vid-VwcWx2aDE6ygVA52eiIUeH8j66gtoH6tw1600h900r217', 'VwcWx2aDE6ygVA52eiIUeH8j66gtoH6t', {"pcode":"ltbGkyOjMl3V3pZ3Trn6T2z1_rjF","playerBrandingId":"ODI0MmQ3NjNhYWVjODliZTgzY2ZkMDdi","width":"1600px","height":"900px"});
(view in My Videos)Wow! Thank you for the extremely thorough explanation and solution!!!
Re: Returning average values based on start and stop times from another tableYou're welcome! It was a great question!
