Creation of a new column based on grouping of other columns

[#1]

Hi All,

I am trying to create a new column "Status" which will return "Active" or "Replaced"  based on the following specification (attached part of the table with "Status" column as illustration of expected result).

Each TOOL and Site can replace different Sub_Tool during time and runs several units with its time stamp. The most recent Sub_Tool for the specific Tool and Site should receive Active on the status column and all previous Sub_Tool which where used on that tool and site should receive Replaced.

As on the example attached same Sub_Tool might repeat over time but only on last runs should be marked as Active.

Real file has many tools and each tool has many sites so need a robust coding.

Your help is much appreciated.  

[#2]

From my reading of your definition, I can come up with row 36 being considered as Active, but other than seeing that row 28 through 35 has the save SubTool value, I don't understand why those rows should be marked as Active.
Or, is what you want to do, a simple matter of finding out what the last SubTool was used for the Tool and Site, and then mark as active all rows back to when the SubTool changes?????
I am confused

[#3]

Hi,

The original table will have many other columns on which I will be calculating statistics with the Sub_Tool hence not only the last row needs to be marked as Acitve but the entire batch on which the latest Sub_Tool ran. Than yes, I need to finde out what the last SubTool was used for the Tool and Site, and then mark as active all rows back to when the SubTool changes. 

[#4]

Here is one of several ways that can probably get the new column calculated.  Just create a new character column, paste the below formula into the new column, and change the references to "Active2" to whatever the name of the new column you created is.

If( Row() > 1,
	If( :TOOL != Lag( :TOOL ) | :Site != Lag( :Site ) | Row() == N Rows( Current Data Table() ),
		TargetValue = :Sub_Tool[Row() - 1];
		:Active2[Row() - 1] = "Active";
		For( i = Row() - 2, i >= 1, i--,
			If( :Sub_Tool[i] == TargetValue,
				:Active2[i] = "Active",
				:Active2[i] = "Replaced";
				Break();
			)
		);
	)
);
If( Row() == N Rows( Current Data Table() ),
	"Active",
	"Replaced"
);

[#5]

Thank you for your dedicated time to solve this issue, the suggested script yields the following miss-intentions to the created column Sub_Tool_Status:

1.   Most recent row value yields Replaced (attached file row 91 marked in yellow)

2.   Most recent row is empty (Attached file last row)

2.  When are several replacements only the last row prior to replacement yields Replaced and it does not update the prior rows (Attached file marked with two green shades)   

[#6]

Thank you for the more complete test file and for your testing of my initial code.

Here is a new version of the format

If( Row() > 1,
	If( :TOOL != Lag( :TOOL ) | :SITE != Lag( :SITE ) | Row() == N Rows( Current Data Table() ),
		TargetValue = :SUB_TOOL[Row() - 1];
		:Active2[Row() - 1] = "Active";
		For( i = Row() - 1, i >= 1, i--,
			If( :SUB_TOOL[i] == TargetValue,
				:Active2[i] = "Active",
				Break()
			)
		);
	)
);
If( Row() == N Rows( Current Data Table() ),
	"Active",
	"Replaced"
);

You will need to change the column references in the code of "Active2" to the column name you are giving the column.

Now my question to you is:

    Do you understand how the formula work?

If not, you need to study the code and ask question about the code, so you understand it, and can make changes in the future that may be necessary.