cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Try the Materials Informatics Toolkit, which is designed to easily handle SMILES data. This and other helpful add-ins are available in the JMP® Marketplace
Choose Language Hide Translation Bar
View Original Published Thread

Script

hcarr01
Level VI
Hello everyone,
 
I have a database where I would like to create a new column from a formula or script.
 
Here is an overview of the database:
 
undefined

 

- column T represents operations to be performed
- column X_3 represents a feature
- The op column represents objects
 
The goal is to create a column "assessment" in which we add "ko" and the number corresponding to "op" when the sum of the defects is different from one line to another for the same "obj" (here value equal to AT).
 
Last particularity, the balance sheet column must be completed for the number of operations "3061".
This is an overview of the database, the column obj is “A” but in the database this column changes value.
 
Thank you for your answers !

This post originally written in French and has been translated for your convenience. When you reply, it will also be translated back to French .

1 ACCEPTED SOLUTION

Accepted Solutions
hcarr01
Level VI

Re: Script

I added this part it works even better:

 

Names Default To Here(1);

dt = current data table();
dt << New Column("BILAN", Character, Nominal);


Summarize(dt, unique_objects = by(:obj));
m_obs = dt[0, "obj"];


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



dt = current data table();
For Each({unique_objects}, unique_objects,
 object_rows = Loc(m_obs, unique_objects); // lignes pour chaque obj
 b = dt[object_rows, "OPE"]; // les différentes opé pour chaque obj
 show(b);
 input_loc = Loc(dt[object_rows, "T"], "3061"); // localiser 3061 pour chaque objet
 
 if( N items(input_loc) < 1,
  continue();
 ,
 
  z = object_rows[input_loc]; // localiser les lignes OPE 3061 pour chaque obj
  value_OPE_3061 = :OPE[z]; // valeur de l'ope 3061 pour chaque obj
  
  liste={}; // transformer en liste pour supprimer les doublons
  for (i = 1, i <= NRows(value_OPE_3061), i++,
   for (j = 1, j <= NCols(value_OPE_3061), j++,
    InsertInto(liste, value_OPE_3061[i, j]);
   );
  );
  
  // supprimer les doublons
  if( N items(liste) <= 1, 
   a = liste;
  ,
   a = Left(liste,1);
  );
  
  // repasser en matrice pour faire les comparaisons
  Mat_3061 = Matrix(a);
  show(Mat_3061);
  

  valid_ope_idx = Loc(b > Mat_3061 & dt[object_rows,somme_col] > 0) ;
  show(valid_ope_idx); 
  
 
  if( N items(valid_ope_idx) < 1,
   continue();
  ,
   ops = dt[object_rows[valid_ope_idx], "op"];
   result_str = "ko - " || Concat Items(ops, " / ko - ");
   dt[object_rows[input_loc], "BILAN"] = Repeat({result_str}, N Items(input_loc));
  );

);
);

Actually you are right @jthi , but I was talking that for a value 3061 of an :obj there cannot be different values of the variable :OPE (afterwards in an obj there can be 2 times for example the value :T equal to 3061 but the value :OPE will be the same).

 

In the end, in output, I would like the display not to repeat when there are several times the value :T equal to 3061 for the same :obj.

 

Example :

 

undefined

 

With a script like this:

input_loc = Loc(dt[object_rows, "T"], "3061");// localiser 3061 pour chaque objet
 
 liste2={}; // transformer en liste pour supprimer les doublons
  for (i = 1, i <= NRows(input_loc), i++,
   for (j = 1, j <= NCols(input_loc), j++,
    InsertInto(liste2, input_loc[i, j]);
   );
  );
  
  // supprimer les doublons
  if( N items(liste2) <= 1, 
   c = liste2;
  ,
   c = Left(liste2,1);
  );
  
  // repasser en matrice pour faire les comparaisons
  loc_3061 = Matrix(c);
  

It works correctly, I take the first index corresponding to :T equal to 3061.

 

But as in the capture above, I would like to be able to remove the duplicate elements on the same line:

kb - 2203 / kb - 2203 --> kb - 2203

 

 

This post originally written in French and has been translated for your convenience. When you reply, it will also be translated back to French .

27 REPLIES 27
jthi
Super User


Re: Script

Why isn't row 6 op 180 being added to last column? 0 does differ from 1.

 

(You should use more informative post titles than just one word)

-Jarmo
hcarr01
Level VI

Re: Script

Because the sum is equal to 0 for line 6. In fact, it is rather when the sum of the defects is different from the value 0 then one informs the value of "op" in the column "balance".

This post originally written in French and has been translated for your convenience. When you reply, it will also be translated back to French .

jthi
Super User


Re: Script

So is there any need to check if the values differ between two rows? Or are you just interested in rows which have :op other than 0?

-Jarmo
hcarr01
Level VI

Re: Script

I think we can only be interested in rows where the value of :sum default is different from 0. Because when the value of: default sum is 0 then nothing is obtained in the balance sheet column.

This post originally written in French and has been translated for your convenience. When you reply, it will also be translated back to French .

jthi
Super User


Re: Script

This script does both and works with the provided example. I strongly suggest you try to understand what it does and ask if there are some parts which you don't.

Names Default To Here(1);

dt = Open("$DOWNLOADS/AAA.jmp");
dt << New Column("bilan2", Character, Nominal);
dt << New Column("bilan3", Character, Nominal);


Summarize(dt, unique_objects = by(:obj));
m_obs = dt[0, "obj"];

/* comparing rows */
For Each({unique_object}, unique_objects,
	object_rows = Loc(m_obs, unique_object);
	values = dt[object_rows, "SOMME DEFAUT"];
	values1 = Remove(values, 1);
	values2 = Remove(values, N Items(values));
	differences = [0] |/ values1 - values2;
	
	ops = dt[object_rows[Loc(differences != 0)], "op"];
	
	result_str = "ko - " || Concat Items(ops, " / ko - ");
	
	input_loc = Loc(dt[object_rows, "T"], "3061");
	If(N Items(input_loc) < 0,
		continue();
	,
		dt[object_rows[input_loc], "bilan2"] = Repeat({result_str}, N Items(input_loc));
	);
 );
 
 
 /* zeros */
For Each({unique_object}, unique_objects,
	object_rows = Loc(m_obs, unique_object);
	values = dt[object_rows, "SOMME DEFAUT"];
	
	ops = dt[object_rows[Loc(values)], "op"];
	
	result_str = "ko - " || Concat Items(ops, " / ko - ");
	
	input_loc = Loc(dt[object_rows, "T"], "3061");
	If(N Items(input_loc) < 1,
		continue();
	,
		dt[object_rows[input_loc], "bilan3"] = Repeat({result_str}, N Items(input_loc));
	);
 );

jthi_0-1689687077723.png

 

Edit: Modified if statement If(N Items(input_loc)... from < 0 to < 1

Edit: Fixed line where values are being inputted to column to use object rows instead of rows from whole table

-Jarmo
hcarr01
Level VI

Re: Script

Thank you for your reply !
 
Comparing the two methods, it seems to me that the second is preferable for my case.
I adapted it to my database, the columns have the same formats on both databases and I get this type of error:

 

undefined

 

This post originally written in French and has been translated for your convenience. When you reply, it will also be translated back to French .

jthi
Super User


Re: Script

As the error message is in french I cannot understand it. It could be that the error is in the end telling you that you don't have bilan3 column. (It is saying that there is invalid subscript but that is caused by not having bilan3 column. This is because dt[input_loc, "bilan3"]  will return [](1, 0) if you don't have the column and then you try to set some values to it).

-Jarmo
hcarr01
Level VI

Re: Script

This is because the "input_loc" variable is empty. So for the assignment of new cells in the "balance sheet3" column it is impossible.

This post originally written in French and has been translated for your convenience. When you reply, it will also be translated back to French .

jthi
Super User


Re: Script

Value "3061" (character, not number) is not being found from column "T". Also the check I have implemented to handle empty input_loc is incorrect, you should modify it to < 1 instead of < 0.

-Jarmo