- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Script
This post originally written in French and has been translated for your convenience. When you reply, it will also be translated back to French .
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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
This post originally written in French and has been translated for your convenience. When you reply, it will also be translated back to French .
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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 .
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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 .
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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));
);
);
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Script
This post originally written in French and has been translated for your convenience. When you reply, it will also be translated back to French .
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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).
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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 .
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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.