Calculation

[#1]

Hello everyone,

I am looking to build a formula against several columns in my database. It's not that easy to understand, I'll explain my goal using an example:

Step 1 corresponds to the number 2100 but this number can have a different corresponding operation depending on the objects (Example: object 1 --> number 2100 / operation 62, object 5 --> number 2100 / operation 74)

STEP 1: corresponds to number 2100

Gray part:

Here, here are 4 different types of object (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 associate this number with operations. When the cells are empty, this means that the object will not undergo this operation.

The objective is :

- Add the durations up to step 1, knowing that the object has made progress (Ex: the ENT2 object 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,

Sincerely

[#2]

Hi @hcarr01,

I'm not sure I understand your request correctly, but how about the following script?

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 ) );
// Add : Matrix of the index
num_colnr_mt = Matrix( Transform Each( {col_name}, dur_cols, Num( 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 )  );
			colidx = Loc( dt[Row(), num_colnr] >= :"opé"n & (num_colnr_mt < 2100) ); // Changed
			If( N Items( colidx ) > 0,
				val = Sum( dt[Row(), dur_cols[colidx]] );
				If( Is Missing( val ),
					0,
					val
				);
			,
				0
			);
		,
			0
		)
	)
);

If you want to calculate the following code, you need to use matrix.

 "num_colnr < 2100"

I hope it helps.

[#3]

Thank you for your help, the code works fine but there is a problem.

the goal is not to use the numbers of the variable T such that T < 2100 because for each object this number has a different corresponding operation.

It would be necessary to make an assignment where for each object we seek the operation corresponding to the number 2100 (in the variable T).

For each line, it would be:

If the current operation is less than or equal to the operation corresponding to number 2100 then sum up to the operation corresponding to number 2100.

Example on the first line:

Operation in progress: 55

Operation corresponding to number 2100 -> 62

So make the sum of the number corresponding to operation 55 therefore number 1500.

Hence "step duration" = "duration 1500" = 30

Except that the numbers are not necessarily arranged in ascending order.

[#4]

Hi @hcarr01 ,

Thank you for your response. Can I check my understanding?

Example on the second line:

Operation in progress: 60 (number in column "opé")
Operation corresponding to number 2100 -> 62 (in variable T and opé)
So make the sum of the number corresponding to operation 60, therefore number 1749 (find number 60 in column 1500, 1749, 2100 or 2500).
Therefore "Duration of step" = "Durée 1749" = 45

Example on the third row:

Operation in progress: 62 (number in the "opé" column)
Operation corresponding to the number 2100 -> 62
So make the sum of the number corresponding to operation 62, therefore number 2100.
Therefore, "Step duration" = 0 (because the current operation is greater than or equal to the operation corresponding to number 2100).

In your question you described as follows.

"Step 1 corresponds to number 2100, but this number can have a different corresponding operation depending on the objects (example: object 1 --> number 2100 / operation 62, object 5 --> number 2100 / operation 74)".

What does object 5 look like? Can you give us an example?

If my understanding is not correct, please let me know.

[#5 · Accepted solution]

Yes your reasoning is correct!

Object 5 represents which object we are studying, and depending on the different objects the operations and the numbers have different correspondences.

I think this code might work:

dt = Current Data Table();
dur_cols = Filter Each( {col_name}, dt << Get Column Names( "String", Continuous ),
 Starts With( col_name, "durée" )
);

num_opé = Filter Each( {col_name}, dt << Get Column Names( "String", Continuous ),
 Starts With( col_name, "opé" )
);

// for indexing later 
num_colnr = Transform Each( {col_name}, dur_cols, Word( -1, col_name ) );
// Add : Matrix of the index
num_colnr_mt = Matrix( Transform Each( {col_name}, dur_cols, Num( Word( -1, col_name ) ) ) )`;
dt << New Column( "DUREE",Numeric,Continuous,Format( ":jou:hh:m", 8 ),<If( dt[Row(), num_opé ] < :"2100"n,//colidx = Loc(dt[Row(), num_colnr] > :"opé"n & (num_colnr < 2100 ) );colidx = Loc( dt[Row(), num_colnr] >= :"opé"n & (dt[Row(), num_colnr] < :"2100"n) ); // ChangedIf( N Items( colidx ) > 0,val = Sum( dt[Row(), dur_cols[colidx]] );If( Is Missing( val ),0,val);,0);,0)));

[#6]

I'm glad you were able to solve the problem yourself.