topic How do I select rows between date ranges in Discussions

How do I select rows between date ranges

steven_w_daniel — Sun, 27 Nov 2016 05:51:02 GMT

Hello All,

I am trying to figure out how to select rows specific to a range of dates using data from a column by last date.

Is there a way to do this in a for loop?

Re: How do I select rows between date ranges

steven_w_daniel — Sun, 27 Nov 2016 12:59:04 GMT

The specific intent is to separate the new part ID from the old part ID

Re: How do I select rows between date ranges

txnelson — Sun, 27 Nov 2016 16:40:20 GMT

Here is a simple solution using Select Where:

Names Default To Here( 1 );
dt = Current Data Table();
dt << select where(
	:Date > Informat( "20Dec2015:00:00:00", "MDYHMS" ) & 
	:Date <= Informat( "20Dec2016:00:00:00", "MDYHMS" )
);

You can also accomplish this interactively by using the pull down menu for the data table:

Rows==>Row Selection==>Select Where

Re: How do I select rows between date ranges

Mark_Bailey — Sun, 27 Nov 2016 18:05:22 GMT

In this case, if the dates are fixed, then you can use a literal or constant value. It looks just like the string above but it is not enclosed in double quotation marks. JMP will interpret (and display) this value as the appropriate date and time. That is, you only need to enter

Date > 20Dec2015:00:00:00

in this case. This way is more concise and elminates the need to deal with formats and concersons. You do not need to use the full specification either. This much is sufficient:

Date > 20Dec2015

Re: How do I select rows between date ranges

steven_w_daniel — Thu, 01 Dec 2016 05:20:23 GMT

I guess I didn't explain myself too well.

First I want to select all rows with the last date;

dt << select where(name ("Date") == col Max(:Date));

Next I want to select only the rows with the PART ID that corresponds to the latest PART ID (id est the max date)

Re: How do I select rows between date ranges

steven_w_daniel — Thu, 01 Dec 2016 05:21:32 GMT

I guess I didn't explain myself too well.

First I want to select all rows with the last date;

dt << select where(name ("Date") == col Max(:Date));

Next I want to select only the rows with the PART ID that corresponds to the latest PART ID (id est the max date)

Re: How do I select rows between date ranges

txnelson — Thu, 01 Dec 2016 11:09:16 GMT

I hope I am interpreting your question correctly. The script below will find the row that contains the Maximum Date's Part_ID and then will select all rows that has that Part_ID value

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

// Find the Row(s) that have the current Max Date
MaxRows = dt << get rows where( dt:Date == Col Max( dt:Date ) );

// MaxRows is a matrix that contains all of the rows that have the
// max date.  Since your "Date" column is actually a date time 
// value that contains minutes and seconds, it is doubtful that
// more than one row will have the Max value.  However, we will
// now just get the last row from that matrix
TheRow = MaxRows[N Rows( MaxRows )];

// Now we can get the Part_ID for the row in question
TargetPartID = dt:Part_ID[TheRow];

// Now select all rows that have that Part_ID
dt << seleect where( dt:Part_ID == TargetPartID );

Re: How do I select rows between date ranges

Mark_Bailey — Thu, 01 Dec 2016 15:26:44 GMT

You can use Boolean expressions as the argument to the << Select Where message. That is, you can use the logical and, or, and not functions or operators to get both the date and the product that meets both criteria. If it is a serial search, then you might have to find the last date first and use it to find the product ID.

You can use the << Get Rows Where( ... ) message to return the row numbers matching your date criterion. You can use this vector to subscript those rows from the data column to get all the part ID as a list. Then examine the list to find the one you want.

Re: How do I select rows between date ranges

steven_w_daniel — Fri, 02 Dec 2016 06:09:11 GMT

Thanks Jim,

Very simple fix, Since the way the measurements are taken I have multiple parameters that have repeated max date. It worked anyway. Below is the script I came up with, however i created another logical nightmare of picking which subset table has the most recent Part ID. Your solution was much more simple.

dt = data table ("XYZ");
// Get unique values
uVals = AssociativeArray(:Part ID << getValues) << getKeys;
// Make Subset Tables
for(g = 1, g <= NItems(uVals), g++,
 r2get = dt << getRowsWhere(:Part ID == uVals[g]);
 dt2 = dt << subset(Rows(r2get));
 dt2 << setName("subset ");