Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Sorting JMP data table and deleting rows in JMP Sc...

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Jul 10, 2014 3:18 PM
(2081 views)

Hi:

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.

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Jul 11, 2014 12:19 PM
(1876 views)

Solution

Something like this should do it:

Get Latest Row = Function**({**dt,ID**}**,**{**Default Local**}**,

// this can be much simpler if we assume datetime order!

//get only those rows matching the specified laser ID

lstRows = dt << **Get Rows Where(**:Laser ID == ID**)**;

//get the corresponding times as a list

lstTimes = Column**(**dt,"Time"**)[**lstRows**]**;

// locate the maximum time

loc = Loc Max**(**lstTimes**)**;

// return the row containing the maximum time

lstRows**[**loc**]**

**)**;

//source data table

dt = Data Table**(**"Initial"**)**;

// get list of unique values for Laser ID

Summarize**(**lstIDs = By**(**:Laser ID**))**;

// create empty final table

newDt = dt << **Subset(**Copy Formulas**(****0))**;

newDt << **Select All Rows**;

newDt << **Delete Rows**;

newDt << **Set Name(**"Final"**)**;

// create a row for each result

Column**(**newDt,"Laser ID"**)** << **Set Values(**lstIDs**)**;

//iterate through each ID to find the row with the latest date

For **(**i=**1**,i<=NItems**(**lstIDs**)**,i++,

currentID = lstIDs**[**i**]**;

r = Get Latest Row**(**dt,currentID**)**;

show**(**r**)**;

//place values from r'th row into new data table

For **(**c=**1**,c<=NCols**(**dt**)**,c++,

Column**(**newDt,c**)[**i**]** = Column**(**dt,c**)[**r**]**

**)**

**)**;

-Dave

3 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Jul 11, 2014 12:19 PM
(1877 views)

Something like this should do it:

Get Latest Row = Function**({**dt,ID**}**,**{**Default Local**}**,

// this can be much simpler if we assume datetime order!

//get only those rows matching the specified laser ID

lstRows = dt << **Get Rows Where(**:Laser ID == ID**)**;

//get the corresponding times as a list

lstTimes = Column**(**dt,"Time"**)[**lstRows**]**;

// locate the maximum time

loc = Loc Max**(**lstTimes**)**;

// return the row containing the maximum time

lstRows**[**loc**]**

**)**;

//source data table

dt = Data Table**(**"Initial"**)**;

// get list of unique values for Laser ID

Summarize**(**lstIDs = By**(**:Laser ID**))**;

// create empty final table

newDt = dt << **Subset(**Copy Formulas**(****0))**;

newDt << **Select All Rows**;

newDt << **Delete Rows**;

newDt << **Set Name(**"Final"**)**;

// create a row for each result

Column**(**newDt,"Laser ID"**)** << **Set Values(**lstIDs**)**;

//iterate through each ID to find the row with the latest date

For **(**i=**1**,i<=NItems**(**lstIDs**)**,i++,

currentID = lstIDs**[**i**]**;

r = Get Latest Row**(**dt,currentID**)**;

show**(**r**)**;

//place values from r'th row into new data table

For **(**c=**1**,c<=NCols**(**dt**)**,c++,

Column**(**newDt,c**)[**i**]** = Column**(**dt,c**)[**r**]**

**)**

**)**;

-Dave

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Jul 11, 2014 1:02 PM
(1326 views)

Here's an alternate approach that uses tabulate with max, and then update:

dt1 = Data Table**(** "Initial" **)**;

tab1 = dt1 << **Tabulate(**

Show Control Panel**(** **0** **)**,

Add Table**(**

Column Table**(** Analysis Columns**(** :time **)**, Statistics**(** Max **)** **)**,

Row Table**(** Grouping Columns**(** :LaserID **)** **)**

**)**, invisible

**)**;

dt2 = tab1 << **Make Into Data Table**;

tab1 << **close window()**;

dt2 << **Update(**

With**(** dt1 **)**,

Match Columns**(** :LaserID = :LaserID, :Name**(** "Max(time)" **)** = :time **)**,

Add Columns from Update table**(**

:PRF,

:Name**(** "Power(W)" **)**,

:Name**(** "Pmon(W)" **)**,

:PRF2,

:Name**(** "Power2(W)" **)**,

:Name**(** "Pmon2(W)" **)**

**)**

**);**

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Jul 12, 2014 11:35 AM
(1326 views)

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

Then create a new column with a formula to select the rows where the * LaserID* in the current row doesn't match the

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**.

Then add a **Formula.**

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.

I hope that helps!

-Jeff

-Jeff