How to fill/replace text between two cells, based on specific values in those two cells

[#1]

Hi All,
I am looking to fill/replace cell  values between two cells based on pattern/text in those two cells. Please have a look into the attached image and data.

Any help, Thanks, - HSS

[#2]

A few questions:

1. Are the triggers in the Current State Code column static, or dynamic?
2. Are you looking for a GUI solution, or a scripting solution?

[#3]

Hi Jed,

Ques - Are the triggers in the Current State Code column static, or dynamic? 
Answer- Dynamic.

Ques - Are you looking for a GUI solution, or a scripting solution?
Answer - Scripting.

Thanks

[#4]

Hi @HSS ,

  Can you provide an example of what the empty column should be filled with? It sounds like you're wanting to fill the empty cells in the column Device_State with some kind of text, but it wasn't clear from your image what the text should be. Also, it sounds like you want that text to change based on the applied current setting, whether it's 0 amp, 0.0001 amp, etc. Is this correct?

  It isn't clear to me how you want the information in the column Current_State_Code to influence the text going into column Device_State, if indeed that is the case.

Thanks!,

DS

[#5]

Hi,

I have created a column "Required-Current_State_Code" - 4th Column in the data table or image. 

I created a 3rd Column using "Contains()" command. This can provide start and end value of empty cells. 

IF( Contains( :Current_State_Code, "amp" ), :Required-Current_State_Code = :Current_State_Code,   ) 

And then fill the missing values. Like Fill "Initialization" in between 'A = 0 amp' and 'B = 0.0001 amp' and so on. These filling values (Initialization, Ramp, Heat etc) are coming from another table. But number of row in between  'A = 0 amp' and 'B = 0.0001 amp' are not fixed, infect this Current value is also not fix, e.g. It can be 'B = 0.01 amp' . Only this is that it should have 'amp' in it.

Hope this will help.

[#6 · Accepted solution]

Just based on the example data provided, we don't need to know between, only starting point. Because we don't know exactly how to define Initialization and so on this example won't be using those but rather row numbers for Device_States

Names Default To Here(1);

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

state_rows = Loc(dt[0, "Device_State"], "Applied_Current");
states_list = Transform Each({val}, As List(state_rows), Char(val));
/* we don't know how to define values which are to be mapped so we will just use row numbers */
current_states = Associative Array(dt[state_rows, "Current_State_Code"], states_list);

new_col = dt << New Column("State", Character, Nominal);

new_col << Set Each Value(
	As Constant(
		state = "";
		ret_val = "";
	);
	If(:Device_State == "Applied_Current",
		ret_val = :Current_State_Code;
		state = current_states[ret_val];
	,
		ret_val = state;
	);
	ret_val;
);

[#7]

@jthi 
Sorry for late replying I was on leave. This seems more generalize way of doing it. I will try to implement something along this way, or will use @Jed_Campbell's solution.
Thanks, HSS

[#8]

Dynamic in what way? You gave one example, so it is difficult to think of a solution covering the changes.

[#9]

Dynamic  means -

Number of rows in between in between 'A = 0 amp' and 'B = 0.0001 amp'.

In fact  'A = 0 amp' and 'B = 0.0001 amp' are also not fixed, it can be 'AA = 0 amp' and 'BB = 0.8 amp' Only thing is we need to fill values between two cells which contains 'amp'. What value we need to fill is coming from another table.

In the give data set, 

Fill  "Initialization" in between 'A = 0 amp' and 'B = 0.0001 amp'

 Fill  "Ramp" in between 'B = 0.0001 amp' and 'C = 0.001 amp'

and so on.

I hope this will help.

Thanks

[#10 · Accepted solution]

Something like this could work, if I'm understanding correctly. You'd need to change the list on line 3 to be the right list each time, and the Try section is there because the loop currently doesn't know what to do about the last grouping. If this is the right approach, you could remove the need for the Try section with some better conditionals. Script below to work with your sample data table:

dt = Data Table( "Sample_Data" );

lstStates = {"Initialization", "Ramp", "Heat", "Other"};

lstLocs = dt << Get Rows Where( Contains( :Current_State_Code, "amp" ) );

Try(
	For Each( {i, j}, lstLocs,
		For Each Row(
			dt,
			If( (Row() > lstLocs[j] & Row() < lstLocs[j + 1]), 
				:Current_State_Code = lstStates[j];
			)
		)
	)
);