639718_en-US

liliconcom

Duration

 

Hello everyone,

 

I have database like below,

 

 

In the comic, the green cells are the results that we would like to obtain.

 

I would like to create a script to calculate durations.

I have "start date" columns and then I have a "number" column which indicates the order of execution.

The goal would be to calculate "start date y - start date x" based on the numbers.

 

I explain myself on an example with the comic strip:

 

1st line : We notice that the column "720" has the number 12 in the cell, so we are looking among all the "number" columns for the first one greater than 12 (here column "740" with the value 21)

So duration 720 would be duration 720 = start date 740 - start date 720.

 

Then for duration 740 it would be duration 740 = start date 9801 - start date 740 with the same principle

 

Here is the script I started writing:

 

dt = Current Data Table();

dur_cols = Filter Each( {col_name}, dt << Get Column Names( "String", Continuous ),
 Starts With( col_name, "Date début" )  
);


num_colnr = Transform Each( {col_name}, dur_cols, Word( -1, col_name ) );

char_cols = dt << Get Column Names ("string", Character); 



wait(1);
N_taches = N Items(dur_cols);
for (i = 1, i <= N_taches, i++, 
 Eval(
  Eval Expr(
   col = dt << New Column ( "durée" || num_colnr[i], Numeric, "Continuous", Format(":jou:hh:m",8));
   col << Formula(
    colidx = Loc(dt[row(), char_cols] > Eval(num_colnr[i]))[1];
    If ( N Items(colidx) > 0,
     val = dur_cols[colidx] - Eval(dur_cols[i]);
     If(IsMissing(val),
      0
     ,
      val
   );
  ,
   0
  );
 );
)));

Thanks for your help !

 

 

Data TableScripting