Add this script to you data table and give it a spin
dt1=current data table();
dt1<< New Column("Lot Actual", formula(If( :Qty == Col Maximum( :Qty, Char( Round( Num( :Lot# ), 0 ) ) ),
:Lot#,
.
)));
dt1<< New Column( "Lot Values in One Formula",
Formula(
If( :Lot Actual == "",
Row State( row() ) = Excluded State( 1 );
Excluded( Row State( row() ) );
1;
)
)
);
dt1<<run formulas();
obj1=dt1 << Tabulate(
Add Table(
Column Table( Analysis Columns( :Qty ) ),
Row Table( Grouping Columns( :Lot# ) )
)
);
dt2=obj1 << Make Into Data Table;
column(dt2,2)<<set name("Lot Max");
dt2<<new column("Rolling Sum of 3",formula(If( Row() == 1,
:Name( "Lot Max" ),
If( Row() == 2,
Lag( :Name( "Lot Max" ), 1 ) + :Name( "Lot Max" ),
:Name( "Lot Max" ) + Lag( :Name( "Lot Max" ), 1 )
+Lag( :Name( "Lot Max" ), 2 )
)
)));
dt1 << Update(
With( dt2 ),
Match Columns( :Lot# = :Lot# )
);
close(dt2, nosave);
obj1<<close window;
dt1<< Delete Columns("Lot Actual", "Lot Max", "Lot Values in One Formula");
///
dtnr=current data table();
dtcollist=dtnr<<Get Column Names;
dtMat = (dtnr << Get All Columns As Matrix);
selection={}; insert into (selection, column(4)); //these columns get acted on
For( i = 1, i <= N Items( selection ), i++,
colNum = Contains( dtColList, Name Expr( selection[i] ) );
vMat = dtMat[0, colNum];
mis = Loc( Is Missing( vMat ) );
If( N Row( mis ),
col = selection[i];
If( mis[1] == 1,
col[1] = col[(Loc( vMat ))[1]]
);
For( j = if(mis[1]==1, 2, 1), j <= N Row( mis ), j++,
col[mis[j]] = col[mis[j] - 1]
);
);
);
... View more