cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
miguello
Level VI

Using "Get Rows Where"

I need to use Get Rows Where to get the row with value that is just below certain value.

In other words:

I have a Date A

I have a table with dates. I need to get the row where the date is the latest, but less than Date A 

 

Example:

I have a date when person died.

I have table with all event when somebody logged in into his account. There are dates before and after his death.

I need to find the latest date he logged in before his death.

Something along the lines of:

lastSelfLogin = dt<<Get Rows Where(:loginDate < deathDate & /*here goes condition that this date needs to be the last*/);

Any ideas?

 

 

Thanks!

3 REPLIES 3
txnelson
Super User

Re: Using "Get Rows Where"

This will do it:

     lastSelfLogin = Max(dt<<Get Rows Where(:loginDate < deathDate));

Jim
miguello
Level VI

Re: Using "Get Rows Where"

Jim,

Thanks for the reply.

I was little bit incorrect in giving my example. Your script indeed will give the latest date of self login. However, I needed the number of row, when it happened, to find other associated values from neighbouring columns.

 

The solution is following:

	//Finding indices for all the logins for current person before death date	
	rowPerson=dt2 << Get Rows Where(:Person==thisPerson & :"DeathDate" < thisDate);
	//Finding index for the latest login date
	rowLatestLoginDate=Loc(loggedOn[rowPerson] == Max(loggedOn[rowPerson]));

//Then later I can access other columns. For instance, weather when person logged in himself for the last time

lastWeather=currentWeather[rowPerson[rowLatestLoginDate]];
txnelson
Super User

Re: Using "Get Rows Where"

I am glad that you found your solution

Jim