I'm a rookie with JMP. I want to sort and format a data table based on conditions on first two columns. The first column is a string of text ("LaserID" ) and the second column is time when data is taken ("time"). My goal is to to derive another table with exact same number of columns as the original table so that there is only one row of data of a particular "LaserID" (i.e. no repeat). The elimination of multiple row(s) with same "LaserID" will be based on when the data is taken ("time").
In another word, the new table will consist of row with a unique "LaserID" with data taken at the latest time.
I am attaching the Original and Final table I want to achieve. Any help in writing a script for this task will be appreciated.
// this can be much simpler if we assume datetime order!
You've gotten a pair of scripting responses. If you'd like to do this interactively, try these steps.
Sort the table by LaserID and Time.
Then create a new column with a formula to select the rows where the LaserID in the current row doesn't match the LaserID in the next row. Since the table is sorted by LaserID and Time you'll get the last row of each LaserID series.
The trick here is to use a column with a data type of Row State and the Selected State state function in the formula.
Choose Cols -> New Column... and set the data type to Row State.
For the formula, start with Row State -> Selected State.
Then put the comparison inside the Selected State() function. This comparison uses the Lag() function with a -1 argument to compare with the value of LaserID in the next row.
Now you just need to copy the row states in your new column to the active row state area by clicking on the star icon in front of the column name.
Now you can use Tables->Subset to subset the selected rows.