36057_en-US

liliconcom

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!

Re: Using "Get Rows Where"

I am glad that you found your solution

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

Re: Using "Get Rows Where"

This will do it:

     lastSelfLogin = Max(dt<