cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Browse apps to extend the software in the new JMP Marketplace
Choose Language Hide Translation Bar
jearls11
Level III

keeping most recent data in column

I want to keep the most recent data and delete the older data.

See image below I want to keep most recent data for highlighted machines and and delete the older row.

example machine 12 I want to delete 2019 data and keep the 2020 data.

jearls11_0-1612381767414.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
ih
Super User (Alumni) ih
Super User (Alumni)

Re: keeping most recent data in column

You could create a new column with a formula using the 'by' argument in the Col Max() function.  In your, case it might look like this:

 

 

Col Max( :LAST_UPDATED_DATE, :Machine ) == :LAST_UPDATED_DATE

 

 

That column would contain either 0 (not latest data) or 1 (latest data).  You could then 'delete' those values using any of a number of methods:

  • Actually delete the rows by right clicking on one of those zeroes and clicking 'select matching cells', then delete the rows.
  • Instead of deleting the rows, just hide and exclude them so you can get back to them later, select the 'old' rows as you did before, but now right click on a row number and select 'hide and exclude'
  • Use data filters to hide those rows.  In many platforms you can add a local data filter to remove or highlight those rows.
  • Subset the table with only the latest data.  This time select all of the rows with a 1, and then use Subset in the Table menu, and choose selected rows.  Now you have one table with just new data, and another with all of the rows

 

Here is what that column would look like:

ih_0-1612386507641.png

 

And here is a script you could run to re-create this example:

View more...
Names default to here(1);

dt = Open("$Sample_data/Air Traffic.jmp");

dt << New Column("Latest Time", Numeric, "Continuous", Format("Best", 12), Formula(Col Max(:Time, :Tail Number) == :Time));

View solution in original post

2 REPLIES 2
ih
Super User (Alumni) ih
Super User (Alumni)

Re: keeping most recent data in column

You could create a new column with a formula using the 'by' argument in the Col Max() function.  In your, case it might look like this:

 

 

Col Max( :LAST_UPDATED_DATE, :Machine ) == :LAST_UPDATED_DATE

 

 

That column would contain either 0 (not latest data) or 1 (latest data).  You could then 'delete' those values using any of a number of methods:

  • Actually delete the rows by right clicking on one of those zeroes and clicking 'select matching cells', then delete the rows.
  • Instead of deleting the rows, just hide and exclude them so you can get back to them later, select the 'old' rows as you did before, but now right click on a row number and select 'hide and exclude'
  • Use data filters to hide those rows.  In many platforms you can add a local data filter to remove or highlight those rows.
  • Subset the table with only the latest data.  This time select all of the rows with a 1, and then use Subset in the Table menu, and choose selected rows.  Now you have one table with just new data, and another with all of the rows

 

Here is what that column would look like:

ih_0-1612386507641.png

 

And here is a script you could run to re-create this example:

View more...
Names default to here(1);

dt = Open("$Sample_data/Air Traffic.jmp");

dt << New Column("Latest Time", Numeric, "Continuous", Format("Best", 12), Formula(Col Max(:Time, :Tail Number) == :Time));
jearls11
Level III

Re: keeping most recent data in column

Many thanks, I have a lot more stuff going on in the background, but had to remove it for IP reasons. This was one was bugging me and clogging up my data.
I will test this out tomorrow.