topic Re: Formula - How to Find Most Recent Row (Based on Conditions) and Retrieve Value from a different Column in Discussions

Formula - How to Find Most Recent Row (Based on Conditions) and Retrieve Value from a different Column

altug_bayram — Sat, 10 Jun 2023 23:54:40 GMT

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

Re: Formula - How to Find Most Recent Row (Based on Conditions) and Retrieve Value from a different Column

jthi — Mon, 26 Sep 2022 05:26:11 GMT

Something like this might work:

dt << New Column("Action", Numeric, Nominal, Formula(
	As Constant(
		latest_ac = 0;
		ret_val = 0;
	);

	If(:Asset == Lag(:Asset),
		If(:Discrete & Date Difference(latest_ac, :DateTime, "day") >= :days_threshold,
			latest_ac = :DateTime;
			ret_val = 1;
		,
			ret_val = 0;
		);
	, 
		latest_ac = 0;
		ret_val = 0;
	);
	ret_val;
));

Re: Formula - How to Find Most Recent Row (Based on Conditions) and Retrieve Value from a different Column

txnelson — Mon, 26 Sep 2022 11:33:33 GMT

I have a question about your DateTime column. As you have it defined, all of the values for the Asset = "XY01" were measured on the 6th day of various months for the year 2022.

row 3 = February 6th, 2022

row 5 = May 6th, 2022

which is 89 days appart, yet you indicate the desired action is 0.

@jthi response shows an Action of 1, as does my own independent calculation.

What are we missing?

Re: Formula - How to Find Most Recent Row (Based on Conditions) and Retrieve Value from a different Column

altug_bayram — Mon, 26 Sep 2022 14:25:53 GMT

The "days" constraint measures in calendar time - "how many days elapsed since time Action was 1" is the question.

I realize I put the format on DateTime column wrong as D/M/Y

It should have been M/D/Y ....

My apologies for the mistake.

so rows 3 and 5 are in June 2nd and 5th respectively.

Would it be possible to create a formula for this ?

thx JMP team.

Re: Formula - How to Find Most Recent Row (Based on Conditions) and Retrieve Value from a different Column

altug_bayram — Mon, 26 Sep 2022 14:35:37 GMT

I have corrected the format of DateTime in this next version. thanks for your help.

Re: Formula - How to Find Most Recent Row (Based on Conditions) and Retrieve Value from a different Column

altug_bayram — Mon, 26 Sep 2022 14:36:46 GMT

Pls see below for my next version w/ the corrected DateTime format (it should have been M/D/Y ... )

I am looking for a formula that can do this, if possible. thx.

Re: Formula - How to Find Most Recent Row (Based on Conditions) and Retrieve Value from a different Column

jthi — Mon, 26 Sep 2022 15:17:41 GMT

My formula should still work just fine, as it was already using days instead of months (I did conversion in my test table, but forgot about it). Action2 is the result from the formula I did provide and there is no difference between it and the Action(Desired) column:

I suggest checking out Scripting Index for As Constant, Date Difference and Lag. Those should help in with the understanding of the formula

Re: Formula - How to Find Most Recent Row (Based on Conditions) and Retrieve Value from a different Column

altug_bayram — Mon, 26 Sep 2022 15:36:16 GMT

Hi , could you pls attach your version so I can see how the script is integrated into the formula. I am novice in putting scripts into formulas.

thanks.

Re: Formula - How to Find Most Recent Row (Based on Conditions) and Retrieve Value from a different Column

jthi — Mon, 26 Sep 2022 15:43:56 GMT

Just the formula part would be this and you should be able to add it directly to the Formula from Column Info after you have created new column

As Constant(
	latest_ac = 0;
	ret_val = 0;
);

If(:Asset == Lag(:Asset),
	If(:Discrete & Date Difference(latest_ac, :DateTime, "day") >= :days_threshold,
		latest_ac = :DateTime;
		ret_val = 1;
	,
		ret_val = 0;
	);
, 
	latest_ac = 0;
	ret_val = 0;
);
ret_val;

I have also attached the table. And if you are very unfamiliar with formulas, you can read some parts of this Using JMP > Create Formulas in JMP

Re: Formula - How to Find Most Recent Row (Based on Conditions) and Retrieve Value from a different Column

altug_bayram — Mon, 26 Sep 2022 15:46:06 GMT

Also, I use Lag, Date Difference plenty times in formulas . But as this does become a bit involved as a code and due to my lack of experience in integrating scripts into formulas, I struggle a bit on that front. If the intent was on number of records rather than calendar days, I could do it easily due to available index function. "Day" requirement makes it harder.

Re: Formula - How to Find Most Recent Row (Based on Conditions) and Retrieve Value from a different Column

altug_bayram — Mon, 26 Sep 2022 16:45:02 GMT

Jarmo

This worked excellent. It also opened my understanding of formulas to contain more complex logic when separated by ";".

Also, it is interesting that 0 in this case works as a starting reset for a DateTime parameter.

Jarmo/Jim - thanks for extremely quick help on this... appreciate the support.