Time of Use Data to Interval Data (Combine two tables)

justin_forbes1

The objective is to take time of use data from the "Raw Data.jmp" file and combine it with a file "IntervalData.jmp" that has 1 second intervals. The idea is to search based on the time stamp and the device ID and create for example a voltage reading column for device 1001 and 1002 ect. Since this is time of use data, if there is not an exact match, the data should be populated in “IntervalData.jmp” with the last data point that matched. This process should continue for the other columns in "Raw Data.jmp" but be placed in separate tables. Thus, the end result there should be 10 separate tables at 1 second intervals.

Any suggestions to create a script or formula in jmp 11 will be much appreciated.

Thanks in advance!

IntervalData.jmp

Raw Data.jmp

eigenmetricsoli

If you are doing a time-based join, the LocSorted function can be useful.  Why do you need 10 separate tables, do you have 10 "devices" 1001, 1002..1010?

Another simple way to do it would be to join the tables by every other matching column (like "device") you desire besides time.  For each unique group item (i.e. device) you will have a row for each combination of interval time and time of use.  You can then select the appropriate combination by, for instance, creating another column like "time difference" = interval_time - use_time.  You can use the time difference column to select the rows that should exist:  for each unique group, the row to keep has the time difference that is positive but minimum in duration.  THen you can just delete the other rows.  This method can be computationally expensive though.  With the tables as small as you have you shouldn't have a problem though.