Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
Emma1
Level III

get rows two weeks before today's date

Hello,

 

In a script, I would like to be able to select rows that are two weeks before today's date.

I would like to run the script and depending on what day it is, that JMP displays the last two weeks only.

 

For that I used in my script the function "subset" and the function :

"get rows where" (: A> 40 &: A <59.5), and I would like to add something else to select the rows corresponding to two weeks before today's date (the date when I will run the script)

 

Thanks for your help

Have a good day

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
txnelson
Super User

Re: get rows two weeks before today's date

You need to make the selection in the data table before performing the Subset....see below

Names Default To Here( 1 );
dt = Current Data Table();

dt << select where( :A > 20 & :A < 40 & :Date >= (Today() - In Weeks( 2 )) );

dtSub = dt << subset(
	selected rows( 1 ),
	columns( :Year, :B, :Month, :Week, :Date, :Product, :A ),
	output table( "abc" )
);

I did not fully test the code, but it should be very close.

Jim

View solution in original post

9 REPLIES 9
Highlighted
txnelson
Super User

Re: get rows two weeks before today's date

In the Scripting Guide, under "Date Time" there is a list of all of the Date Time functions that are available.  I assume you want to do something like:

theRows = dt << get rows where( :Date >= Today() - InWeeks(2));

or to adjust back to the beginning of the current date (i.e. Midnight)

theRows = dt << get rows where( :Date >= Today() - Time Of Day( Today() ) - InWeeks(2));
Jim
Highlighted
Emma1
Level III

Re: get rows two weeks before today's date

Thanks, I tested this but it still doesn't work
What I'm looking to do is create a new base of my main database with a few columns I'm interested in and specifying that I only want column A with values between 20 and 40 and two weeks before today.
I have this:

dt = Current Data Table ();
dtSub = dt << subset (
Output Table ("abc"),
rows (dt << get rows where (: A> 20 &: A <40 &: Date> = Today () - InWeeks (2)));
columns (: Year,: B,: Month,: Week,: Date,: Product,: A)
);

 

Thanks 

Highlighted
txnelson
Super User

Re: get rows two weeks before today's date

You need to make the selection in the data table before performing the Subset....see below

Names Default To Here( 1 );
dt = Current Data Table();

dt << select where( :A > 20 & :A < 40 & :Date >= (Today() - In Weeks( 2 )) );

dtSub = dt << subset(
	selected rows( 1 ),
	columns( :Year, :B, :Month, :Week, :Date, :Product, :A ),
	output table( "abc" )
);

I did not fully test the code, but it should be very close.

Jim

View solution in original post

Highlighted
Emma1
Level III

Re: get rows two weeks before today's date

Hello,

Sorry but I tried your code but still can't do it ..
I'm new to script programming, so I don't have the reflexes that others can have in this kind of little script

Can I still ask you for help?
Thank you

Highlighted
txnelson
Super User

Re: get rows two weeks before today's date

Please attach a sample data table, and I will see where the issue is.

 

Also, why are you trying to do this by writing a script?  This can all be done interactively.

Jim
Highlighted
Emma1
Level III

Re: get rows two weeks before today's date

I would like the script to first allow to generate another database to extract only what interests me, then to send the report by email to then close the new database without saving it

 

Thank you

Highlighted
txnelson
Super User

Re: get rows two weeks before today's date

Please attach a sample data table.  I am assuming the issue is in the data.  If it is not working, and there are no messages in the log, then we need to look at what is happening with the data.

Jim
Highlighted
Emma1
Level III

Re: get rows two weeks before today's date

Here is an example of my database

Have a good day
Thank you

Highlighted
txnelson
Super User

Re: get rows two weeks before today's date

The issue is that the columns specified in the Subset phrase were not the names of the columns in the data table.  Your names are in French, so all that has to be done, is to change the names of the columns in the script to match the names in the data table.

Names Default To Here( 1 );
dt = Current Data Table();

dt << select where( :A > 20 & :A < 40 & :Date >= (Today() - In Weeks( 2 )) );

dtSub = dt << subset(
	selected rows( 1 ),
	columns( :Année, :B, :Quantième, :Mois, :Date, :Produit, :A ),
	output table( "abc" )
);
Jim
Article Labels