Script

[#1]

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:

- 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 !

[#2]

There is no point adding For Each Row back until you get other parts of the script working. You can continue with the example I did provide until you have correct value set to row 9 (due to working with unique_objects[2] which is B). To get OPE indices which are higher than the  "3061" rows OPE you can use Loc like this.

valid_ope_idx = Loc(opes > opes[input_loc]);

Do note that these are indices within opes matrix NOT rows from the data table. To convert them back rows you can use those indices with object_rows as object_rows is a matrix with rows for the specific unique object

[#3]

Yes, I agree with you !

The values I find with my formula are correct and I find the same as you.

However it is in the construction of the continuation of the script where I have more difficulty!

Because I have to return the values "ko-..... /ko-...» when :OPE is greater than :OPE corresponding to :T = 3061 and when the :DEFAULT SUM is different from 0 (as in the example).

[#4]

Them you continue with similar idea

1. Add check that there are more than 0 items in valid_ope_idx
2. Get all values for valid_ope_idx
3. Then from this get the indices of values which are larger than 0
4. And then with this you can finally get the values from :op for these specific indices (remember to convert them to rows first)
5. Concatenate these values together using Concat Items()
6. Add value to correct row

[#5]

In my test database it works fine with the following code:

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
 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
 show(value_OPE_3061); 

 valid_ope_idx = Loc(b > value_OPE_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));
 );

);

However, in my final database, it is possible that for the same obj several times the value :T = 3061 is displayed (on the other hand the values of OPE are the same!).

I think that something else needs to be changed, the value of value_OPE_3061 must be unique (and not for example value_OPE_3061 = [ 50 , 50 , 50 ] but rather value_OPE_3061 = [ 50 ]).

Is there a function on JMP to remove duplicates from an array/list?

Thank you for your answers !

[#6]

I thought earlier you said that it is impossible that there would be multiple "3061" values?

One option to remove duplicates is to use Associative Array() with << get keys (doesn't work if you have floats),  other good in this case would be to use Design(<<levels).

[#7 · Accepted solution]

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 :

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

[#8]

You could just pick the first index which has 3061 value for the object.

[#9]

Something like this is how I would have solved this. Should work with the example data

Names Default To Here(1);

// dt = Open("$DOWNLOADS/AAA.jmp");
// dt = Current Data Table();

dt = New Table("AAA(1)",
	Add Rows(12),
	Compress File When Saved(1),
	New Column("obj", Character(16), "Nominal", Set Values({"A", "A", "A", "A", "A", "A", "A", "B", "B", "B", "B", "C", "C"})),
	New Column("T",	Character(4), "Ordinal", Set Values({"3052", "3061", "3082", "3940", "4012", "5010", "2890", "4060", "3061", "3061", "3081", "5010", "3061"})),
	New Column("OPE", Numeric, "Continuous", Format("Best", 12), Set Values([12, 20, 32, 44, 56, 125, 210, 54, 60, 60, 80, 45, 50])),
	New Column("X__3", Character(4), "Nominal", Set Values({"rien", "rien", "incl", "ret", "ret", "rien", "incl", "incl", "ret", "ret", "incl", "ret", "rien"})),
	New Column("SOMME DEFAUT", Numeric, "Continuous", Format("Best", 12), Set Values([0, 0, 1, 2, 1, 0, 2, 1, 2, 2, 1, 1, 0])),
	New Column("op", Character(12), "Nominal", Set Values({"40", "32", "55", "212", "510", "180", "2100", "2240", "2100", "2100", "500", "", ""}))
);

dt << New Column("bilan", Character, Nominal);

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

For Each({unique_object}, unique_objects,
	object_rows = Loc(m_obs, unique_object);
	input_loc = Loc(dt[object_rows, "T"], "3061");
	
	If(N Items(input_loc) == 0,
		continue();
		//Throw("3061 not found");
	, N Items(input_loc) > 0,
		input_loc = input_loc[1];
	);
	
	opes = dt[object_rows, "OPE"];
	valid_ope_idx = Loc(opes > opes[input_loc]);

	If(N Items(valid_ope_idx) < 1,
		result_str = ".";
	,
		values = dt[object_rows[valid_ope_idx], "SOMME DEFAUT"];
		ops = dt[object_rows[valid_ope_idx[Loc(values)]], "op"];
		result_str = "ko - " || Concat Items(ops, " / ko - ");
	);
	
	dt[object_rows[input_loc], "bilan"] = result_str;
);