topic Re: get rows two weeks before today's date in Discussions

get rows two weeks before today's date

Emma1 — Fri, 09 Jun 2023 23:36:25 GMT

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

Re: get rows two weeks before today's date

txnelson — Mon, 31 Aug 2020 10:53:17 GMT

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

Re: get rows two weeks before today's date

Emma1 — Mon, 31 Aug 2020 18:53:49 GMT

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

Re: get rows two weeks before today's date

txnelson — Mon, 31 Aug 2020 15:16:55 GMT

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.

Re: get rows two weeks before today's date

Emma1 — Wed, 02 Sep 2020 20:37:41 GMT

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

Re: get rows two weeks before today's date

txnelson — Wed, 02 Sep 2020 20:45:18 GMT

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.

Re: get rows two weeks before today's date

Emma1 — Wed, 02 Sep 2020 21:02:38 GMT

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

Re: get rows two weeks before today's date

txnelson — Wed, 02 Sep 2020 21:46:58 GMT

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.

Re: get rows two weeks before today's date

Emma1 — Thu, 03 Sep 2020 13:13:09 GMT

Here is an example of my database

Have a good day
Thank you

Re: get rows two weeks before today's date

txnelson — Thu, 03 Sep 2020 13:21:50 GMT

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