380107_en-US

liliconcom

Increment a value for Run Number

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?

 

 

 

Data TableScriptingRe: Increment a value for Run Number

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

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;