cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
ENTHU
Level IV

Date function in jsl.

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
txnelson
Super User

Re: Date function in jsl.

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

View solution in original post

10 REPLIES 10
txnelson
Super User

Re: Date function in jsl.

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
ENTHU
Level IV

Re: Date function in jsl.

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?

txnelson
Super User

Re: Date function in jsl.

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
ENTHU
Level IV

Re: Date function in jsl.

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.

txnelson
Super User

Re: Date function in jsl.

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

Jim
ENTHU
Level IV

Re: Date function in jsl.

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 );

 

Jeff_Perkinson
Community Manager Community Manager

Re: Date function in jsl.

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
ENTHU
Level IV

Re: Date function in jsl.

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.

Jeff_Perkinson
Community Manager Community Manager

Re: Date function in jsl.

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