cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
The Discovery Summit 2025 Call for Content is open! Submit an abstract today to present at our premier analytics conference.
Choose Language Hide Translation Bar
View Original Published Thread

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

Neo
Neo
Level VI

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