Subscribe Bookmark RSS Feed

Using "Get Rows Where"

miguello

Community Trekker

Joined:

Jan 27, 2016

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
txnelson

Super User

Joined:

Jun 22, 2012

This will do it:

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

Jim
miguello

Community Trekker

Joined:

Jan 27, 2016

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

Joined:

Jun 22, 2012

I am glad that you found your solution

Jim