659685_en-US

liliconcom

Script

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 !

Automation and ScriptingRe: 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)

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".

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?

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.

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:

 

 

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).

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.

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.

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[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[input_loc, "bilan3"] = Repeat({result_str}, N Items(input_loc));
 );
 );

 

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

Re: Script

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

Re: Script

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.

Re: Script

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.

 

Re: Script

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 !

Re: Script

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).

Re: Script

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...;
);

 

Re: Script

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

Re: Script

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.

Re: Script

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(...//

Re: Script

 

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);<BR />    //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 !

Re: Script

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

Re: Script

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).

 

Re: Script

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

Re: Script

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 !

Re: Script

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

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 :

 

 

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

 

 

Re: Script

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

Re: Script

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;
);