Hi - I need help to put a formula to find a most recent row based on conditions and retrieve a different column value / perform calculations between two row parameters (DateTime).
Below data table (also attached) has : Asset, DateTime, Discrete and Action columns .
Data is sorted based on Asset first and then DateTime (ascending)
There is also a Table variable days_threshold = 4.
Action(Desired) is the desired form of Action in which the current formula
needs to be modified.
Any row that gets a Discrete = 1 (which is normally determined by a formula rules skipped here and instead sample values provided) , Action need to start looking to its own prior rows (in DateTime descending form). Two conditions in that search are :
1- Looking for the same Asset for which Discrete =1 (i.e. either XY01 or XY02)
2- Look until most recent value of Action = 1 is found .
Compute number of days between last time Action was 1 versus DateTime of current row.
If the difference (in days) >= days_threshold = 4 ---> then Action of current row = 1
Else if the difference (in days) < days_threshold ---> then Action of current row = 0
Only interested in finding most recent time when Action was "1" ... Once that single value is found (if exists), search can stop at that point. The limit to search is until the first occurrence of that Asset (ie. oldest record). If Action was never set to "1" earlier, then Action of current row can be set to "1"
e.g. row 2 is the first time XY01 gets Action =1 (based on Discrete, which itself is tied to some rules , just showing w/ values in this example) .
next at row 3, Discrete is set to 1 again, but this time Action needs to be 0 as the number of days difference between prior and current rows is 0 (< days_threshold).
Similarly, for row 5, Action still remains 0 as only 3 days elapsed from last time Action was 1.
Finally Action sets to "1" at row 6.
Provided two assets following this idea- set the desired values to Action(Desired).
I prefer this to be a formula rather than a script.
thanks in advance JMP team.
Altug Bayram
