I have two data sets that I need to merge. One data set has date/time to the nearest minute. The other data set has date/time to the nearest hour. To rapidly align the various columns in the two sets, I would like to take the second data set (to the nearest hour) and expand it to show the hourly values 60 times, once for each minute of the hour. Is there an easy way to do this (the hourly data set has hundreds of rows, so manual alignment is not feasible).
Here is how I would do it. If you take the data table that has the date/time values to the nearest minute, you can add a new column that is just to the nearest hour. Using the MDYHMS() function as the formula
MDYHMS( month(:datetime), day(:datetime), year(:datetime),hour(:datetime),0,0)
Swap out :datetime for the actual column name that has your data down to the minute.
Next, all you have to do is to join this data table, with the data table that has the data to the hour.
Tables==>Join
Use the data table with the data down to the minute as the main table and the data table down to the hour as the With table. Match the newly created column in the main table to the date time column in the With table. JMP will automatically create a match for each of the 60 rows for each hour from the main datqa table.
