631439_en-US

liliconcom

Re: Formule colonne

One question: Why does ENT4 contain duration from 2500 but ENT2 doesn't contain the duration from 1749?

Re: Column formula

An error in the database, I updated the database!

Thanks for the remark.

 

 

Re: Formule colonne

You can do this with a formula, but I would do this with scripting. Below is one example which does at least work with your example data

Names Default To Here(1);

dt = Open("$DOWNLOADS/Test(1).jmp");

// get duration columns
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, Output("Matrix"), Num(Word(-1, col_name)));

dt << New Column("DUREE TOT", Numeric, Continuous, << Set Each Value(
 colidx = Loc(num_colnr > :T); // indices of num colums larger than :T value
 If(N Items(colidx) > 0,
  val = Sum(dt[Row(), dur_cols[colidx]]);
  If(IsMissing(val),
   0
  ,
   val
  );
 ,
  0
 )
));

Re: Column formula

Thank you for your reply.

I have a remark, it seems to me that in your code you are comparing with the elements of column T, however I forgot to specify that the values of T can be in any order (not necessarily increasing), so that the values of the operations are arranged in ascending order of advancement.

 

For example :

T = 690 / op = 47

T = 550 / op = 55

And progress follows the order of operations!

 

Second remark, in my database I have numerical values in duration format for durations T, with the format "h:m:s".

 

 

Re: Formule colonne

If I understand correctly, my solution should still work. It is only reliant on the fact that :1500, :1749,... columns are in same order as :duree 1500, :duree 1749,... columns. Also even if JMP does show you cleanly formatted duration values in reality (I think) they are still seconds (you can check this by changing Format to Best for example from column settings).

Re: Column formula

I guess there is a problem in the code.

 

The problem here would be in the assignment of the "colidx" variable: we do not compare the numbers of the variable T but the operations corresponding to these numbers (here the yellow columns).

 

In the example above for the first line it would be:

 

if first line ":1500" > first line ":op" -> add duration 1500

if first line ":1749" > first line ":op" -> add duration 1749

if first line ":2100" > first line ":op" -> add duration 2100

if first line ":2500" > first line ":op" -> add duration 2500

 

and do this for each line...

Re: Formule colonne

So change

colidx = Loc(num_colnr > :T);

to 

colidx = Loc(num_colnr > :"opé"n);

and make corresponding changes to Loc (use specific row instead of column names) and num_colnr (no need to be numbers)

 

Re: Column formula

 

Thank you for your answer, I have already changed the assignment of "colidx" as you indicated.

 

However, I don't see what changes I should make to:"and make corresponding changes to Loc (use specific row names instead of column names) and num_colnr (no numbers needed)."

 

Can you tell me in more detail please?

Re: Column formula

I modified the value of num_colnr with this code to select the columns needed for the following:

 

 

SNames = dt << Get Column Names(String, "Nominal");

 

For(i=1, i <= N Items(Snames), i++,

sclmn = Column(Snames[i]);

show(sclmn);

);

 

This code allows me to get all the columns corresponding to the yellow part of the database.

 

However, I can't seem to make the corresponding changes for the "Loc".

Re: Formule colonne

Names Default To Here(1);

dt = Open("$DOWNLOADS/Test (1).jmp");

// get duration columns
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 TOT", Numeric, Continuous, << Set Each Value(
 colidx = Loc(dt[Row(), num_colnr] > :"opé"n);
 If(N Items(colidx) > 0,
  val = Sum(dt[Row(), dur_cols[colidx]]);
  If(IsMissing(val),
   0
  ,
   val
  );
 ,
  0
 )
));

Data table subscripting 

Re: Column formula

On my database I have 900 different columns (yellow columns), this means that the number "num_colnr" is a list with many numbers:

 

 

By executing the JSL script that you shared with me, I get this type of error:

 

 

While num_colnr as pictured above is a list of number

 

Re: Column formula

And by adding "Num" to convert to numeric:

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

I get this type of error:

 

 

Re: Formule colonne

Do you have column named "711" in your data table? I do not understand that error message as it is in French and my best guess is that it is same as this

Expecting a number or list of numbers at row 1 in access or evaluation of 'dt[ /*###*/Row(),num_colnr]', <BR />Bad Argument("1500"), dt[/*###*/Row(), num_colnr]

at line 14 in C:\Users\jarmo\Documents\Script.jsl

which could mean that column named "711" is missing.

Re: Column formula

 

Yes this is my first column (among all the yellow colored columns):

 

Re: Formule colonne

Ok, maybe it is then indicating that one (or more) of those columns are missing and not really telling which one. So maybe my logic how I get those from the duree columns is incorrect

Re: Column formula

Yes you are probably right, I will look on my side.

Thank you for you precious help !

Column formula

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:

 

 

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 up the durations, 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)

 

- Let's take the ENT2 object, we would have to add the duration 2100 and the duration 2500 because these are the numbers that this object must undergo.

 

 

Thanks for your help,

Sincerely

Automation and Scripting