Hello everyone,
I'm looking to build a formula against multiple columns in my database. It's not that easy to understand, I'll explain my goal with an example:
STEP 1: corresponds to number 2100
Gray part:
Here, here are 4 different object types (ENT1,..,ENT4). Each of the objects is associated with a progress number (column T), these numbers are linked to well-defined operations.
Yellow part:
These cells show which "numbers" each object will undergo, and associates this number with operations. When the cells are empty, it means that the object will not undergo this operation.
The objective is :
- Sum the durations up to step 1, knowing that the object has had an advancement (Ex: the object ENT2 is located at number 1749, so what it did before in 1500 does not interest us ) so the duration until step 1 is only the duration of step 1749 so duration = 45
I started with this code:
Names Default To Here( 1 );
dt = Current Data Table();
dur_cols = Filter Each({col_name}, dt << Get Column Names("String", Continuous),
Starts With(col_name, "durée"));
// for indexing later
num_colnr = Transform Each({col_name}, dur_cols,Word(-1, col_name));
dt << New Column("DUREE ETAPE1", Numeric, Continuous, Format(":jou:hh:m",8), << Set Each Value(
If( :"T"n < 2100 ,
colidx = Loc(dt[Row(), num_colnr] > :"opé"n & (num_colnr < 2100 ) );
If(N Items(colidx) > 0,
val = Sum(dt[Row(), dur_cols[colidx]]);
If(IsMissing(val),
0
,
val
);
,
0
)
,
0
)
));
Thanks for your help,
Cordially
