topic Re: Date function in jsl. in Discussions

Date function in jsl.

ENTHU — Thu, 17 May 2018 00:18:36 GMT

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.

Re: Date function in jsl.

txnelson — Thu, 17 May 2018 01:30:28 GMT

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;

Re: Date function in jsl.

ENTHU — Fri, 18 May 2018 21:56:32 GMT

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?

Re: Date function in jsl.

txnelson — Sat, 19 May 2018 01:37:57 GMT

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

Re: Date function in jsl.

ENTHU — Sat, 19 May 2018 02:13:23 GMT

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.

Re: Date function in jsl.

txnelson — Sat, 19 May 2018 03:06:10 GMT

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

Re: Date function in jsl.

ENTHU — Sat, 19 May 2018 15:56:49 GMT

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

Re: Date function in jsl.

Jeff_Perkinson — Sat, 19 May 2018 17:44:25 GMT

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

Re: Date function in jsl.

ENTHU — Sat, 19 May 2018 20:20:28 GMT

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.

Re: Date function in jsl.

Jeff_Perkinson — Sat, 19 May 2018 20:48:29 GMT

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

Re: Date function in jsl.

txnelson — Sat, 19 May 2018 20:48:56 GMT

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