topic Increment a value for Run Number in Discussions

Increment a value for Run Number

dkraeuter_sunne — Sat, 10 Jun 2023 23:29:18 GMT

I am trying to develop a formula that increments up the Run #. It should count up for each time a Shop Order has a new Process ID and reset back to 1 when a new Shop Order has not been run before. As shown below the Shop Order 532 has 3 different Process IDs, but they are not in order so it still increments up to 3 on the final one even though it isn’t in order. I created a column manually showing what I would want as an ideal outcome for the formula column. How would I write a formula to deal with this issue?

Re: Increment a value for Run Number

txnelson — Tue, 27 Apr 2021 18:33:40 GMT

Here is a formula you can use. Create the new column Run #. I suggest you make the modeling type "Ordinal". Open the Formula Editor for the new column, and paste in the following.

If( Row() == 1,
	:Process ID << set property( "Value Order", {Common Order( 0 ), Row Order Levels( 1 )} );
	Summarize( byGroup = by( :Process ID ) );
	:Process ID << delete property("Value Order");
);

Below is a script that will do the column creation and apply the formula for you

Names Default To Here( 1 );
dt = Current Data Table();

dt << New Column( "Run #",
	ordinal,
	formula(
		If( Row() == 1,
			:Process ID << set property( "Value Order", {Common Order( 0 ), Row Order Levels( 1 )} );
			Summarize( byGroup = by( :Process ID ) );
			:Process ID << delete property("Value Order")
		);
		Contains( byGroup, :Process ID );
	)
);

Re: Increment a value for Run Number

dkraeuter_sunne — Tue, 27 Apr 2021 19:16:54 GMT

Thank you for this. It doesn't quite do what I am looking for. See the mismatch between the "Ideal Outcome Column for Run #" and the "Run #". The way the formula is currently working is just incrementing up no matter if a new SO is started.

Re: Increment a value for Run Number

txnelson — Tue, 27 Apr 2021 21:00:14 GMT

Sorry, I missed the link with Shop Order. That changes everything and makes the formula much simpler

If( Row() == 1 | :Shop Order != Lag( :Shop Order ),
	count = 1,
	If( :Process ID != Lag( :Process ID ),
		count
		++)
);
count;

Re: Increment a value for Run Number

dkraeuter_sunne — Tue, 27 Apr 2021 22:10:13 GMT

That was closer, but it restarted the count for Shop Order # 532 at 1 the second time that it was listed since there was another Shop Order between runs 2 and 3. The image below shows the highlighted cells where it deviated from the Ideal Outcome.

Re: Increment a value for Run Number

txnelson — Wed, 28 Apr 2021 03:36:40 GMT

OK....my oversight again....I think the approach below solves the issue

Names Default To Here( 1 );
dt = Current Data Table();

// Create a column that when the data table is sorted, it can be returned to the original
dt << New Column( "theRow", formula( Row() ) );

// Remove the formula to turn the values into static values
:theRow << delete formula;

// Sort the data into an order where all Process ID for a given Shop Order are together
dt << sort( by( :Shop Order, :Process ID, :theRow ), replace table( 1 ) );

// Create the Run # column
dt << New Column( "Run #",
	formula(
		If( Row() == 1 | :Shop Order != Lag( :Shop Order ),
			count = 1,
			If( :Process ID != Lag( :Process ID ),
				count
				++)
		);
		count;
	)
);

// Remove the formula to turn the values into static values
:"Run #"n << delete formula;

// Sort back into the original row order
dt << sort( by( :theRow ), replace table( 1 ) );

// Delete the no longer needed theRow column
dt << delete columns( :theRow );