topic Re: Creation of a new column based on grouping of other columns in Discussions

Creation of a new column based on grouping of other columns

DZ_932 — Tue, 12 May 2020 20:21:04 GMT

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.

Re: Creation of a new column based on grouping of other columns

txnelson — Tue, 12 May 2020 21:09:43 GMT

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

Re: Creation of a new column based on grouping of other columns

DZ_932 — Tue, 12 May 2020 21:51:32 GMT

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.

Re: Creation of a new column based on grouping of other columns

txnelson — Wed, 13 May 2020 01:19:15 GMT

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

Re: Creation of a new column based on grouping of other columns

DZ_932 — Wed, 13 May 2020 06:23:20 GMT

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)

Re: Creation of a new column based on grouping of other columns

txnelson — Wed, 13 May 2020 09:56:28 GMT

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.