topic Re: Selecting data for past X number of days in Discussions

Selecting data for past X number of days

djmomo — Wed, 05 Feb 2020 00:36:02 GMT

I have a data set that contains past 60 days of data and I want just past 7 days of data. Data column name "OUT_DATE" in the format of MM/DD/YYY HH:MM:SS AM/PM. Date is not sorted and in random order.

I found the following discussion answered by super user "txnelson" (https://community.jmp.com/t5/Discussions/Date-function-in-jsl/td-p/57122) and did my script as below

dt=Open("Report.csv");// Opening the main datafile
P7D = dt << select where( today() - In Days( 7 ) < :OUT_DATE );// Selecting past 7 days data and assigning it to P7D
P7D << Save("P7D.csv");// Save P7D as csv

But my output file "P7D.csv" has an exact copy of the "Report.csv".

How can I store only past 7 days of data in P7D?

Re: Selecting data for past X number of days

txnelson — Thu, 06 Feb 2020 13:56:40 GMT

Selection of given rows does not affect the saving. The easiest way to do that, is to create a subset of the data, based upon the selection, and then save that subset. (The below is corrected code: see @gzmorgan0 )

names default to here(1);
dt=Open("Report.csv");// Opening the main datafile
dt << select where( today() - In Days( 7 ) < :OUT_DATE );// Selecting past 7 days data and assigning it to P7D
P7D = dt << subset(invisible, selected columns(0), selected rows(1));
P7D << Save("P7D.csv");// Save P7D as csv
close( P7D, nosave);

Re: Selecting data for past X number of days

gzmorgan0 — Thu, 06 Feb 2020 13:52:46 GMT

Just a simple correction, @txnelson nice solution is missing an equal sign. Line 4 should be

P7D = dt << subset(invisible, selected columns(0), selected rows(1));

Re: Selecting data for past X number of days

txnelson — Thu, 06 Feb 2020 13:54:47 GMT

Thanks for the catch