turn on suggestions

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

Showing results for

- JMP User Community
- :
- Discussions
- :
- Re: Date function in jsl.

Topic Options

- 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

Wednesday
(143 views)

I have a table with that has time stamp for all the rows.

But I need only last 7 days data. Is there an easy way to delete all data that is past last 7 days.

New to jsl and any inputs will be helpful.

1 ACCEPTED SOLUTION

Accepted Solutions

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

Saturday
(34 views)

Solution

Here is a complete script that should show you a result

```
Names Default to Here( 1 );
dt = New Table( "test",
add rows( 100 ),
New Column( "start date",
formula( Random Uniform( Today() - In Days( 12 ), Today() ) ),
Format( "m/d/y", 10 )
)
);
dt:start date << delete formula;
New Column( "START_SHORT_DATE",
Formula( :start date - Time Of Day( :start date ) ),
Format( "m/d/y", 10 ),
);
New Column( "Todays Date",
Formula( Today() - Time Of Day( Today() ) ),
Format( "m/d/y", 10 )
);
New Column( "Ref Date",
Formula( Today() - In Days( 7 ) ),
Format( "m/d/y", 10 ),
);
Current Data Table() << select where( :Ref Date < :START_SHORT_DATE );
```

Jim

10 REPLIES

Highlighted

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

Wednesday
(138 views)

```
dt << select where( today() - In Days( 7 ) < :Your date column );
dt << delete rows;
```

This will select all rows where your date column(replace its name in the first line of the JSL), is less than the current datetime - 7 days. Then the delete rows will delete all selected rows.

If you want the rows to be deleted to be 7 days prior to midnight, not 7 days before this point in time (today()), then you need to subtact the current time from the today() functions value.

```
dt << select where( today() - Time of Day( today() ) - In Days( 7 ) < :Your date column );
dt << delete rows;
```

Jim

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

Friday
(97 views)

This piece of script isnt working for me.

the " today() - Indays(7) part works but something wrong with Date comparision.

Debugger doesnt show any error but nothing happens when I run this. I am using JMP 12. Is that a problem?

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

Friday
(82 views)

I don't have JMP 12 available to me to verify if In Days() function is available. However, since all In Days() is doing is determining the number of seconds in a day, and then multiplying the result times the number in the (7), you can just change the formula to:

`today()-7*60*60*24 `

Jim

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

Friday
(78 views)

Thanks for the response.As I mentioned above, I created a new column and printed today() - In Days(7) which works perfectly fine.

After this if I try comparing 2 columns,I dont see any result/error. All date columns are numeric.

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

Friday
(74 views)

Can you show the exact code you are using that is not working, and could you supply a sample of your data table?

Jim

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

Saturday
(59 views)

Here's the code -

```
Clear Symbols();
Delete Symbols();
clear globals();
//Open master data list
S = Open("C:\Enthu\sample.xlsx","invisible");
current data table(S);
New Column( "START_SHORT_DATE", Numeric, Formula( Short Date( :start date ) ), );
New Column("Todays Date", Character,Formula(Abbrev Date(today())));
New Column("Ref Date", Character,Formula(Abbrev Date(today()-In Days(7))));
S << select where( :Ref Date < :START_SHORT_DATE );
```

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

Saturday
(53 views)

The problem is that the functions Short Date() and Abbrev Date() return character strings not date values. You can't compare a date value (which is numeric) to a character string.

Try this instead:

```
New Column( "START_SHORT_DATE",
Formula( :start date - Time Of Day( :start date ) ),
Format( "m/d/y", 10 ),
);
New Column( "Todays Date",
Formula( Today() - Time Of Day( Today() ) ),
Format( "m/d/y", 10 )
);
New Column( "Ref Date",
Formula( Today() - In Days( 7 ) ),
Format( "m/d/y", 10 ),
);
current data table() << select where( :Ref Date < :START_SHORT_DATE );
```

-Jeff

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

Saturday
(44 views)

I copied the exact same code and tried running.All columns are created properly.But the following line doesnt seem to work -

"

`current data table() << select where( :Ref Date < :START_SHORT_DATE );`

Nothing gets selected.I tried using greater than , less than and equal to operators.Nothing seems to work.

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

Saturday
(39 views)

Sorry I didn't catch this earlier. You'll need a Run Formulas() to ensure that all formulas are evaluated before the Select Where().

```
Clear Symbols();
Delete Symbols();
clear globals();
//Open master data list
S = Open("~/Downloads/sample.xlsx");
New Column( "START_SHORT_DATE",
Formula( :start date - Time Of Day( :start date ) ),
Format( "m/d/y", 10 ),
);
New Column( "Todays Date",
Formula( Today() - Time Of Day( Today() ) ),
Format( "m/d/y", 10 )
);
New Column( "Ref Date",
Formula( Today() - In Days( 7 ) ),
Format( "m/d/y", 10 ),
);
s << run formulas();
s << select where( :Ref Date < :START_SHORT_DATE );
```

BTW: You don't really need all the formula columns to do this.

```
Clear Symbols();
Delete Symbols();
Clear Globals();
//Open master data list
S = Open( "~/Downloads/sample.xlsx" );
s << select where(
(Today() - Time Of Day( Today() ) - In Days( 7 )) < :start date - Time Of Day( :start date )
);
```

-Jeff