topic Re: Number by Count in Other Columns in Discussions

Number by Count in Other Columns

JMPUser9 — Fri, 19 Jan 2024 16:29:27 GMT

I want to create a formula in a new column, Run Number, to count based off other columns. For example:

Sample Test Run Number

A one 1

A one 2

A two 1

A two 2

B one 1

B two 1

C one 1

C one 2

C two 1

C two 2

Re: Number by Count in Other Columns

jthi — Fri, 19 Jan 2024 16:37:10 GMT

Using Col Cumulative Sum() is one option

Col Cumulative Sum(1, :Sample, :Test)

Col Rank() is also an option

Col Rank(:Sample, :Sample, :Test)

Full example with your table

Names Default To Here(1);

dt = New Table("Untitled 2",
	Add Rows(10),
	Compress File When Saved(1),
	New Column("Sample",
		Character,
		"Nominal",
		Set Values({"A", "A", "A", "A", "B", "B", "C", "C", "C", "C"})
	),
	New Column("Test",
		Character(16),
		"Nominal",
		Set Values({"one", "one", "two", "two", "one", "two", "one", "one", "two", "two"})
	),
	New Column("Run Number",
		Numeric,
		"Continuous",
		Format("Best", 12),
		Set Values([1, 2, 1, 2, 1, 1, 1, 2, 1, 2])
	)
);

dt << New Column("RN", Numeric, Ordinal, Formula(
	Col Cumulative Sum(1, :Sample, :Test)
));

Re: Number by Count in Other Columns

txnelson — Fri, 19 Jan 2024 17:17:55 GMT

@JMPUser9 ,

@jthi has provided an excellent answer.

However, the answer to this question has been answered multiple times before in the Discussion Community. I request that before a request is submitted that a search is made to the past Discussions.

I did a search on "Count" and it returned several related responses.

Thank you