Creation of a new column based on grouping of other columns

Question

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.

tool.csv

txnelson · Answer

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.

DZ_932 · Answer

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)

Book2.xlsx

txnelson · Answer

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"
);