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

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

Neo — Mon, 18 Sep 2023 15:45:57 GMT

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?

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

mmarchandTSI — Mon, 18 Sep 2023 16:03:40 GMT

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

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

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

txnelson — Mon, 18 Sep 2023 16:32:05 GMT

@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 )

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

jthi — Mon, 18 Sep 2023 16:32:20 GMT

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