How to correct sequence/order in a column with standard reference and if it does not follow over write it with some rules.

[#1]

Dear All,

I recently asked a question how to fill missing values in a column and got/implemented the solution. But I am stuck again. This problem is the continuation of the same (2nd stage issue.)

Reference of previous problem -
https://community.jmp.com/t5/Discussions/How-to-fill-replace-text-between-two-cells-based-on-specifi...

I want to generate a column "Device_State_Final_Corrected" in the attached data, based on "Device_ID",  "Device_State_Final" and following rules -

---------------------------------------------------------------------------------------

For Each device, well define Order is -

"Initialization - Ramp - Heat - Ramp_2 - Ramp_Down -Final"

Two rules to follow -

Rule 1. If a higher state is appeared once (say, "Ramp_2"), lower state (say, "Initillization") can not appear. If it is appearing, it would be replace with the previously occurrent higher state (by "Ramp_2") - see row number - 12. Exception occurs for device state "Heat"

Rule 2. Now if somewhere if device is heating up, it should repeat the sequence after "Heat" and following rule 1.

"Initialization - Ramp - Heat - Ramp_2 - Ramp_Down -Heat - Ramp_2 - Ramp_Down - Final" -- See the row number 24, 25, 26.

---------------------------------------------------------------------------------------
Any help,
Thanks - HSS

[#2 · Accepted solution]

What does "heating up" mean?

Edit:

I just decided heating up means that current row has value Heat and previous was Heat.

Names Default To Here(1);

dt = Open("$DOWNLOADS/Sample_Data2.jmp");

dt << New Column("A", Character, Nominal, << Set Each Value(
	As Constant(
		priorities = {"Initialization", "Ramp", "Heat", "Ramp_2", "Ramp_Down", "Final"};
	);
	If(Row() == Col Min(Row(), :Device_ID),
		priority_idx = Contains(priorities, :Device_State_Final);
	,
		If(!Contains(priorities, :Device_State_Final),
			priority_idx;
		,
			If(:Device_State_Final == "Heat" & Lag(:Device_State_Final) == "Ramp",
				priority_idx = Contains(priorities, "Heat");
			, Contains(priorities, :Device_State_Final) >= priority_idx,
				priority_idx = Contains(priorities, :Device_State_Final);
			);
		);
	);
	priorities[priority_idx];
));

[#3]

Thanks Jarmo, 
This seems working fine. I will implement and test it to a wide set of data.
Thanks again.

[#4]

Dear All, Wish you a very Happy New Year.

@jthi and Rest of the community members - I stuck again!!

The formula/script you provided (4th Column - "Formula_3") worked fine for 80% of the data but failing where the appeared (wrong) order is higher in sequence. In the given example - "Settling_Time" is higher in the sequence order but it appeared at wrong place (In between "Main_Ramp" at rows 4 and 6; 10 and 12) and now the current logic does not allow to edit that. I thought, I can use another loop like -
If (

Lag(:Current_State) == Lag(:Current_State, -1) & :Current_State != Lag(:Current_State), :Current_State = Lag(:Current_State)

);

But it is also failing due to the order of sequence in row number 5, 6, and 7. My required Column is 3rd column in the attached data set, named as "Required Column".

Any help and/or suggestion ?
Thanks