topic Re: Using "Get Rows Where" in Discussions

Using "Get Rows Where"

miguello — Sat, 18 Feb 2017 03:48:48 GMT

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!

Re: Using "Get Rows Where"

txnelson — Sat, 18 Feb 2017 04:27:31 GMT

This will do it:

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

Re: Using "Get Rows Where"

miguello — Sat, 18 Feb 2017 04:43:11 GMT

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]];

Re: Using "Get Rows Where"

txnelson — Sat, 18 Feb 2017 12:45:47 GMT

I am glad that you found your solution