rolling week

[#1]

Hello everyone,

I have a database that contains data from early 2019 to April 2023.
I created a script that when I run it again adds new data for 2023 every week.
To prevent this file from becoming too heavy, I would like when I restart the script at the beginning of the week to be able to delete a rolling week.

For example today we are in week 21 (W21) of 2023. When I run the script today, I can delete the S1 of 2019 and then add the S21 of 2023.
Next week we will be in S22 2023, the script will add this new week, at the same time I would like to be able to delete the S2 of 2019, so on...

However, if I run the script several times in the same week, it should not delete several consecutive weeks.
Example : If I run the script 3 times in S21 2023, it just deletes S1 2019 and not S1/S2/S3 2019 (because I ran the script 3 times)

I have a "Start date" column in my database which will be useful for the development of the script. Here is an overview of the column below:

Thanks for your help

[#2]

I do not know the purpose of the updated data table, so this suggestion might not be helpful. Instead of deleting rows, you could compute a rolling row state for excluded rows in a formula column or with a script. You said you want to avoid the data table becoming too heavy, so this approach might not suit your purpose.

[#3]

To avoid weighing down the data table, my goal is to add new data but on the other hand delete the "old" data. This would allow the data table to have balance against the file size.

I would like to write a script to do what I said in my first post.

[#4]

Are you extracting data from a database into a JMPM data table? If so, you can use a WHERE clause to specify the period.

[#5 · Accepted solution]

Consider using the Date Increment function. In this example, Date Increment counts 230 weeks into the past and returns the date of the first day of that week (Sunday). Then we just select all rows with Start Dates before that date and delete them. Within any single week you run the script, the date returned by Date Increment will be the same, so you can update your data table multiple times per week without triggering unwanted data deletion.

Names Default to Here( 1 );
deleteDate = Date Increment( Today(), "Week", -230, "start" );
Data Table( "Rolling Week.jmp" ) << Select Where( :Start Date < deleteDate );
Data Table( "Rolling Week.jmp" ) << Delete Rows;