turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- How do I select rows between date ranges

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Nov 26, 2016 9:51 PM
(2707 views)

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?

2 ACCEPTED SOLUTIONS

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Nov 30, 2016 9:20 PM
(5159 views)

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)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Dec 1, 2016 3:09 AM
(5145 views)

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Nov 27, 2016 4:59 AM
(2699 views)

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Nov 27, 2016 5:46 AM
(2689 views)

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Nov 27, 2016 10:05 AM
(2677 views)

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`

Learn it once, use it forever!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Nov 30, 2016 9:20 PM
(5160 views)

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)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Nov 30, 2016 9:21 PM
(2602 views)

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)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Dec 1, 2016 3:09 AM
(5146 views)

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Dec 1, 2016 10:09 PM
(2554 views)

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Dec 1, 2016 7:25 AM
(2587 views)

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.

Learn it once, use it forever!