cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Try the Materials Informatics Toolkit, which is designed to easily handle SMILES data. This and other helpful add-ins are available in the JMP® Marketplace
Choose Language Hide Translation Bar
Neo
Neo
Level VI

How to fill sequential numbers (starting from 1) in a column when identical items appear in another column?

Lets say I have a two column data. The first column partID, second column is a measured value for that part. Each part is measured N times so that partID column has rows with the ID appearing N times in the table until the next partID apepars. I want to create a new column with number of measurement repeats/Runs for a given part. 

 

So, for identical partIDs appearing in the partID column, I want to fill the Runs column with sequential numbers starting from 1. 

So if part A is measured 11 times, the Runs column goes gets row entries from 1 to 11. Next part B is measured 9 times, the Runs column goes has row entries 1 to 9 and so on.

 

How can I automate this via JSL?

When it's too good to be true, it's neither
1 ACCEPTED SOLUTION

Accepted Solutions
jthi
Super User

Re: How to fill sequential numbers (starting from 1) in a column when identical items appear in another column?

Col Cumulative Sum with value of 1 should work with current JMP versions

Names Default To Here(1);

dt = New Table("Untitled",
	Add Rows(10),
	Compress File When Saved(1),
	New Column("Column 1",
		Character,
		"Nominal",
		Set Values({"A", "A", "A", "A", "B", "B", "C", "C", "C", "D"})
	)
);

dt << New Column("Run", Numeric, Ordinal, Formula(
	Col Cumulative Sum(1, :Column 1)
));
-Jarmo

View solution in original post

3 REPLIES 3
mmarchandTSI
Level V

Re: How to fill sequential numbers (starting from 1) in a column when identical items appear in another column?

As long as the partIDs are grouped as you stated, this will work.

If(
	Row() == 1, 1,
	Lag( :partID ) == :partID, Lag( :Runs ) + 1,
	1
)
txnelson
Super User

Re: How to fill sequential numbers (starting from 1) in a column when identical items appear in another column?

@mmarchandTSI solution is a fine solution.  But as usual, in JMP there are multiple ways to get to the solution.  Here is an alternative solution

Col Cumulative Sum( 1, :partID )
Jim
jthi
Super User

Re: How to fill sequential numbers (starting from 1) in a column when identical items appear in another column?

Col Cumulative Sum with value of 1 should work with current JMP versions

Names Default To Here(1);

dt = New Table("Untitled",
	Add Rows(10),
	Compress File When Saved(1),
	New Column("Column 1",
		Character,
		"Nominal",
		Set Values({"A", "A", "A", "A", "B", "B", "C", "C", "C", "D"})
	)
);

dt << New Column("Run", Numeric, Ordinal, Formula(
	Col Cumulative Sum(1, :Column 1)
));
-Jarmo