How to assign and increment an iteration count for multiple variables with unequal rows?

[#1]

Hello all.

Im trying to create a column which assigns an iteration number (numeric and continuous) based on column "Comment = End of Cycle" and increments it for the same SN. The number of data points for each SN is not the same. 

For example.

for SN = "A", rows 1-9: Iteration =1, rows 10-16 Iteration =2

for SN = "B", rows 17-27: Iteration =1, rows 28-36 Iteration =2 and so on...

Tried a few variations using the  Row ( ) function but was not successful. Can someone provide some guidance on this?

[#2]

Hi @sumanthcb141977 
please try the following.

New Table( "Untitled",
	Add Rows( 36 ),
	New Column( "SN",
		Character,
		"Nominal",
		Set Values(
			{"A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A",
			"A", "A", "B", "B", "B", "B", "B", "B", "B", "B", "B", "B", "B", "B",
			"B", "B", "B", "B", "B", "B", "B", "B"}
		)
	),
	New Column( "iteration",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values(
			[1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 2, 1, 1, 1, 1, 1, 1, 1, 1,
			1, 1, 1, 2, 2, 2, 2, 2, 2, 2, 2, 2]
		)
	),
	New Column( "iteration count",
		Numeric,
		"Nominal",
		Format( "Best", 9 ),
		Formula( Col Cumulative Sum( 1, :SN, :iteration ) )
	)
)

If this is not what you are looking for please provide the data table so it is much easier to address.
let us know if it worked

[#3]

Thanks for the response. Sorry had to be more specific. I dont need an additional column by the name of iteration count. I wanted the formula for the iteration to assign a number and increment it based on the "end of cycle" comment. Below is the image of what i wanted the iteration column to look like.

Im attaching the jmp file per the request.

[#4 · Accepted solution]

@sumanthcb141977 

try the following. it is not robust and requires the data to be sorted by SN and then date.

New Table( "Untitled",
	Add Rows( 36 ),
	New Column( "SN",
		Character,
		"Nominal",
		Set Values(
			{"A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A",
			"A", "A", "B", "B", "B", "B", "B", "B", "B", "B", "B", "B", "B", "B",
			"B", "B", "B", "B", "B", "B", "B", "B"}
		)
	),
	New Column( "Comment",
		Character,
		"Nominal",
		Set Values(
			{"Cycle", "Cycle", "Cycle", "Cycle", "Cycle", "Cycle", "Cycle", "Cycle",
			"End of Cycle", "Cycle", "Cycle", "Cycle", "Cycle", "Cycle", "Cycle",
			"End of Cycle", "Cycle", "Cycle", "Cycle", "Cycle", "Cycle", "Cycle",
			"Cycle", "Cycle", "Cycle", "Cycle", "End of Cycle", "Cycle", "Cycle",
			"Cycle", "Cycle", "Cycle", "Cycle", "Cycle", "Cycle", "Cycle"}
		)
	),
	New Column( "iteration count",
		Numeric,
		"Nominal",
		Format( "Best", 9 ),
		Formula(
			If(
				Row() == 1, 1,
				Lag( :SN ) == :SN & Lag( :Comment ) == "Cycle",
					Lag( :iteration count ),
				Lag( :SN ) == :SN & Lag( :Comment ) == "End of Cycle",
					Lag( :iteration count ) + 1,
				Lag( :SN ) != :SN & Lag( :Comment ) == "End of Cycle", 1
			)
		),
		Set Selected
	)
)

perhaps others have a more robust way of indexing this.

[#5]

Thanks for the recommendation. The data is sorted by SN and date which makes it easier.

[#6]

@sumanthcb141977 

my only other idea would be to use an additional table. perhaps use new table as is an use its value as place markers. this will allow you to have one table or numeric values and another with categories. this will reduce the hard conversions between numbers and categories.

[#7 · Accepted solution]

Here is other option for a formula

Col Cumulative Sum(
	Col Min(Row(), :SN) == Row() | Lag(:Comment) == "End of Cycle",
	:SN
);