Multiple columns with their own dedicated timestamp - Join/Combine/Concat timestamps

Question

I have many files with the below format, where each measurement has its own recorded timestamp. I'd like a table that has only one time column, so that I can chart multiple variables against the same timeline.

Here's an example of my data:

datetime_measure1value_measure1datetime_measure2value_measure2datetime_measure3value_measure31:39:15.000 PM1.71381:39:15.000 PM400.03451:39:15:000 PM50.90071:39:23.071 PM0.09761:39:25.730 PM399.87931:39:17.591 PM51.74121:39:26.410 PM1.71631:39:35.990 PM399.94871:39:33.590 PM51.19361:39:37.150 PM0.12451:39:40.029 PM400.03611:39:50.869 PM50.9051

Ideally I'd like it to look something like this:

datetime value_measure1value_mesure2value_measure31:39:15.000 PM1.7138400.0345 50.90071:39:17.591 PM--51.74121:39:23.071 PM0.0976--1:39:25.730 PM-399.8793-1:39:26.410 PM1.7163--1:39:33.590 PM--51.19361:39:35.990 PM-399.9487-1:39:37.150 PM0.1245--1:39:40.029 PM-400.0361-1:39:50.869 PM--50.9051

I have been attempting to use the stack, join, and concatenate formulas in JMP to do this. But it has produced undesirable results. Usually I end up with "value" columns that are unsorted correctly. I may need to manipulate the data first to add some kind of sorting column, but haven't been successful yet. Need help as I am not a JMP scripting expert.

Thank you.

MathStatChem · Accepted Answer

You can do this with two table operations:Stack all the columns (Tables > Stack), and check the box for "Multiple Series Stack".  Enter the value 2 for the number or series, and make sure the "contiguous" box is not checked.  Click OK.  This will create a table that looks like this:

Then, you want to split this table (Tables > Spit).  Split by Label 2, split the Data 2 column, and group by Data.

this results in this table:

Note, you will have another problem to deal with, I think.  I don't believe JMP supports the datetime format  (hh:mm:ss.ddd AM/PM) you are using, if you want to treat those values like numbers.  If you treat them like character values you will be alright, though, and the steps above will still work.