cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
DZ_932
Level I

Creation of a new column based on grouping of other columns

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.  

5 REPLIES 5
txnelson
Super User

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

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
Jim
DZ_932
Level I

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

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. 

 

txnelson
Super User

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

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

 

Jim
DZ_932
Level I

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

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)   

txnelson
Super User

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

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.

Jim