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]

In the database, the values of the :T column are not necessarily arranged in ascending order for the same type of object. This is another :OPE column which has its elements arranged in ascending order. The :T and :OPE columns are closely related, they are unique for each type of object.

The :OPEs which are lower than the :OPE values corresponding to the value 3061, nothing should be put in the balance sheet column.

When the values of :OPE are greater than the values :OPE corresponding to the value 3061 then the operation already described in the first messages is carried out.

See the comic below as an example:

Thank you for your answers !!

[#3]

Add additional checks for :OPE values. If :OPE value for row is lower than the :OPE value for "3061" ignore it. It might be a bit difficult to keep track with different indices and rows, but adding debug prints helps with this. Also if there can be more than one "3061" for one object, the calculation gets more complicated.

[#4]

Yes I see what you mean!

I imagine something that looks like this:

If( col = Loc(dt[Row(), :OPE] < Loc(dt[Row(), :T=="3061")),
 continue();
 ,
  script...
);

On the other hand, it is impossible for the same object to have the number 3061 twice.

[#5]

It does not work, do you know how I can make such a script @jthi (the last checks to be done)?

Thank you again for your help !

[#6]

Get all :OPE values for unique_object, then get the :OPE value for the 3061 row. After that check from the list of all :OPE values the indices which are higher than the :OPE value for 3061 (Loc() can do this).

[#7]

Yes that's what I said to myself, I thought of something like that but the difficulty remains to define the variables a and b.

dt = current data table();
b = // all values :OPE for unique_object
a = // values :OPE for 3061
test = Loc(dt[Row(), b]) > Loc(dt[Row(),a]);

If( test=0 ,
  continue();
 ,
  script...;
);

[#8]

Similar method as used for values variable which stores :SOMME DEFAUT values for specific unique object

	values = dt[object_rows, "SOMME DEFAUT"];

add debug prints and try running the script one line at the time by using only one object

[#9]

Yes thank you I wrote a code like this which allows me to have the information I need:

dt = current data table();
For Each({unique_objects}, unique_objects,
 object_rows = Loc(m_obs, unique_objects);
 b = dt[object_rows, "OPE"];
 show(b);
 
 a = dt << Get Rows Where( :T == "3061");
 valeurs_OPE = :OPE[a];
 show(valeurs_OPE);
 valeurs_OPE_3061_obj = dt[unique_objects, "valeurs_OPE"];
 show(valeurs_OPE_3061_obj);
 
);

However, I cannot get the OPE values corresponding to task 3061 by: obj (variable "valeurs_OPE_3061_obj").

Only the variable "valeurs_OPE" gathers all the OPE data corresponding to task 3061.

[#10]

It is easier to test if you just ignore For Each (for now). Start with something like this

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", "C", "C"})),
	New Column("T",	Character(4), "Ordinal", Set Values({"3052", "3061", "3082", "3940", "4012", "5010", "2890", "4060", "3061", "3081", "5010", "3061"})),
	New Column("OPE", Numeric, "Continuous", Format("Best", 12), Set Values([12, 20, 32, 44, 56, 125, 210, 54, 60, 80, 45, 50])),
	New Column("X__3", Character(4), "Nominal", Set Values({"rien", "rien", "incl", "ret", "ret", "rien", "incl", "incl", "ret", "incl", "ret", "rien"})),
	New Column("SOMME DEFAUT", Numeric, "Continuous", Format("Best", 12), Set Values([0, 0, 1, 2, 1, 0, 2, 1, 2, 1, 1, 0])),
	New Column("op", Character(12), "Nominal", Set Values({"40", "32", "55", "212", "510", "180", "2100", "2240", "2100", "500", "", ""})),
	New Column("correct", Character, "Nominal", Set Values({"", "ko - 55 / ko - 212 / ko - 510 / ko - 2100", "", "", "", "", "", "", "ko - 500", "", "", "."}))
);

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

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

unique_object = unique_objects[2];
object_rows = Loc(m_obs, unique_object);
input_loc = Loc(dt[object_rows, "T"], "3061"); // location of 3061 for unique_object
show(input_loc); // index within object_rows
show(object_rows[input_loc]); // real row number

opes = dt[object_rows, "OPE"]; // all OPE values for unique_object
valid_ope_idx = Loc(...//

[#11]

I continued with the following script:

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"];



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);    //show(valid_ope_idx); 
 
 
 For( i = 1, i <= N Items(b), i++,
  If(N Items(input_loc) < 1 & b[i] <= value_OPE_3061,
   continue();
  ,
   values = dt[object_rows, "SOMME DEFAUT"];
   ops = dt[object_rows[Loc(values)], "op"];
   result_str = "ko - " || Concat Items(ops, " / ko - ");
   dt[object_rows[input_loc], "BILAN"] = Repeat({result_str}, N Items(input_loc));
  );
  
 );
);

First I used the Loc() function to determine the row indices but then I don't know how to continue the script. So I tried with a "For" loop and the result is still not correct. Can you help me move forward?

Thank you in advance for all responses !