BookmarkSubscribeRSS Feed

Re: Date function in jsl.

ENTHU

Occasional Contributor

Joined:

May 13, 2018

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

Joined:

Jun 22, 2012

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
txnelson

Super User

Joined:

Jun 22, 2012

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

Occasional Contributor

Joined:

May 13, 2018

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

Joined:

Jun 22, 2012

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

Occasional Contributor

Joined:

May 13, 2018

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

Joined:

Jun 22, 2012

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

Occasional Contributor

Joined:

May 13, 2018

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

Joined:

Jun 23, 2011

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

Occasional Contributor

Joined:

May 13, 2018

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

Joined:

Jun 23, 2011

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