Discussions
Solve problems, and share tips and tricks with other JMP users.
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.
Accepted Solutions
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:
And here is a script you could run to re-create this example:
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));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:
And here is a script you could run to re-create this example:
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));