Subscribe Bookmark RSS Feed

How do I select rows between date ranges

steven_w_daniel

Community Trekker

Joined:

Jun 25, 2015

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?

 

Capture.JPG

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
steven_w_daniel

Community Trekker

Joined:

Jun 25, 2015

Solution

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)

txnelson

Super User

Joined:

Jun 22, 2012

Solution

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 );
Jim
8 REPLIES
steven_w_daniel

Community Trekker

Joined:

Jun 25, 2015

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

txnelson

Super User

Joined:

Jun 22, 2012

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 

Jim
markbailey

Staff

Joined:

Jun 23, 2011

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
steven_w_daniel

Community Trekker

Joined:

Jun 25, 2015

Solution

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)

steven_w_daniel

Community Trekker

Joined:

Jun 25, 2015

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)

 

txnelson

Super User

Joined:

Jun 22, 2012

Solution

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

Community Trekker

Joined:

Jun 25, 2015

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

 

 

markbailey

Staff

Joined:

Jun 23, 2011

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.