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.
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 );
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 )
);
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 -
"
<SPAN class="token function">current data table</SPAN><SPAN class="token punctuation">(</SPAN><SPAN class="token punctuation">)</SPAN> <SPAN class="token operator"><</SPAN><SPAN class="token operator"><</SPAN><SPAN class="token messages"> select where</SPAN><SPAN class="token punctuation">(</SPAN> <SPAN class="token punctuation">:</SPAN>Ref Date <SPAN class="token operator"><</SPAN> <SPAN class="token punctuation">:</SPAN>START_SHORT_DATE <SPAN class="token punctuation">)</SPAN><SPAN class="token punctuation">;</SPAN>
Nothing gets selected.I tried using greater than , less than and equal to operators.Nothing seems to work.
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 );
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 );
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?
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.
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
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?
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;
