Running Count of Repeated Values JSL Script-Part2<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />I am glad that somebody came out with this counting question days before, however, I would like to take a step further to ask a similar question that many manufacturing/production line would encounter.
Using the same color example, having different color product like red, blue, green, yellow,white. By using the count_color script we can directly calculating count for each color.
Then, if I am going to cumulative 5 count of the same color product into a box as a shipment unit. When a certain color product reach its maximum boxing capacity, the upcoming product with the same color will automatically reset the count back to 1. In order to distinguish production sequence of each box that contains the same color of production, the Box column should hold a format like RED-1, RED-2,....
One more constraint is the count_color need to distinguish/sort the product is created in which Shift (Day, or Night). Please refer to the table below.
I am hoping a useful jsl script can be designed to help to meet this kind of request for generating count_color column as well as Box column.
Thanks
| color | count_color | Shift | Box |
|---|
| ..... | ..... | | |
| RED | 4 | NIGHT | RED-4 |
| RED | 5 | NIGHT | RED-4 |
| RED | 1 | DAY | RED-1 |
| RED | 2 | DAY | RED-1 |
| BLUE | 1 | NIGHT | BLUE-3 |
| YELLOW | 1 | DAY | YELLOW-2 |
| BLUE | 2 | NIGHT | BLUE-3 |
| GREEN | 1 | NIGHT | GREEN-2 |
| RED | 3 | DAY | RED-1 |
| BLUE | 1 | DAY | BLUE-1 |
| GREEN | 1 | NIGHT | GREEN-2 |
| WHITE | 1 | NIGHT | WHITE-1 |
| WHITE | 1 | DAY | WHITE-1 |
| RED | 4 | DAY | RED-1 |
| RED | 5 | DAY | RED-1 |
| RED | 1 | DAY | RED-2 |
| BLUE | 3 | NIGHT | BLUE-3 |
| YELLOW | 2 | DAY | YELLOW-2 |
| RED | 1 | NIGHT | RED-1 |
Re: Running Count of Repeated Values JSL Script-Part2Thanks, MS.
Re: Running Count of Repeated Values JSL Script-Part2My formula gives the expected result if you paste it into the formula editor. To add it by script, as you seems to have done, you need to use the NameExpr() trick to ensure the expression is not evaluated before going into the formula property (cf. this recent blog). If you look at the formula in the column MS_Box_Script you'll notice that it is not what I posted above.
dt = Current Data Table();
dt << New Column("The real MS_Box_Script",
Formula(Name Expr(Eval(Eval Expr(:color || "-" || Char(Ceiling(Col Sum(Row() <= Expr(Row()), :color, :Shift) / 5))))))
);
Re: Running Count of Repeated Values JSL Script-Part2Thanks, it work out great!
Re: Running Count of Repeated Values JSL Script-Part2Hi MS,
Thanks for writing the box script for me!
However, the Script you wrote does not work out as excepted.
The design is to have a box store 5 (max) same color product, then a new ! empty will come in to store the upcoming same color product. The box description, even for storing the same color product, should move from 1 to 2 or 2 to 3, like n+1. However, the script you provide all have the same box # for the same color.
The Box script I used provided from you is
dt=Current Data Table();
dt<
Numeric,
"Continuous",
Format( "Best", 4 ),
Formula(Eval(Eval Expr(:color || "-" || Char(Ceiling(Col Sum(Row() <= Expr(Row()), :color,:Shift)/ 5)+1)))));
I have attached the file of example, the "Excepted Box Result" column should be the result that the script should be able to execute.
Can you help up with this? Many Thanks,
| Color | Shift | Excepted Box Result | MS_Box_Script |
| BLUE | DAY | BLUE-1 | BLUE-1 |
| BLUE | DAY | BLUE-1 | BLUE-1 |
| BLUE | DAY | BLUE-1 | BLUE-1 |
| BLUE | NIGHT | BLUE-1 | BLUE-1 |
| BLUE | NIGHT | BLUE-1 | BLUE-1 |
| BLUE | NIGHT | BLUE-1 | BLUE-1 |
| BLUE | NIGHT | BLUE-1 | BLUE-1 |
| GREEN | DAY | GREEN-1 | GREEN-1 |
| GREEN | DAY | GREEN-1 | GREEN-1 |
| GREEN | DAY | GREEN-1 | GREEN-1 |
| GREEN | DAY | GREEN-1 | GREEN-1 |
| GREEN | DAY | GREEN-1 | GREEN-1 |
| GREEN | DAY | GREEN-2 | GREEN-1 |
| GREEN | DAY | GREEN-2 | GREEN-1 |
| GREEN | DAY | GREEN-2 | GREEN-1 |
| GREEN | NIGHT | GREEN-1 | GREEN-1 |
| RED | DAY | RED-1 | RED-1 |
| RED | DAY | RED-1 | RED-1 |
| RED | DAY | RED-1 | RED-1 |
| RED | DAY | RED-1 | RED-1 |
| RED | DAY | RED-1 | RED-1 |
| RED | DAY | RED-2 | RED-1 |
| RED | DAY | RED-2 | RED-1 |
| RED | DAY | RED-2 | RED-1 |
| RED | NIGHT | RED-1 | RED-1 |
| RED | NIGHT | RED-1 | RED-1 |
| RED | NIGHT | RED-1 | RED-1 |
| RED | NIGHT | RED-1 | RED-1 |
| RED | NIGHT | RED-1 | RED-1 |
| YELLOW | DAY | YELLOW-1 | YELLOW-1 |
| YELLOW | DAY | YELLOW-1 | YELLOW-1 |
| YELLOW | DAY | YELLOW-1 | YELLOW-1 |
| YELLOW | NIGHT | YELLOW-1 | YELLOW-1 |
| YELLOW | NIGHT | YELLOW-1 | YELLOW-1 |
| YELLOW | NIGHT | YELLOW-1 | YELLOW-1 |
| YELLOW | NIGHT | YELLOW-1 | YELLOW-1 |
| YELLOW | NIGHT | YELLOW-1 | YELLOW-1 |
| YELLOW | NIGHT | YELLOW-2 | YELLOW-1 |
| YELLOW | NIGHT | YELLOW-2 | YELLOW-1 |
Re: Running Count of Repeated Values JSL Script-Part2Great formula! I think this would work for the Box:
Eval(Eval Expr(:color || "-" || Char(Ceiling(Col Sum(Row() <= Expr(Row()), :color,:Shift) / 5))))
Re: Running Count of Repeated Values JSL Script-Part2Building upon what they did the other day.
If( Mod( Eval( Eval Expr( Col Sum( Row() <= Expr( Row() ), :Color, :Shift ) ) ), 5 ),
Mod( Eval( Eval Expr( Col Sum( Row() <= Expr( Row() ), :Color, :Shift ) ) ), 5 ),
5
)
*Edit* or
Mod( Eval( Eval Expr( Col Sum( Row() <= Expr( Row() ), :Color, :Shift ) ) ) - 1, 5 ) + 1
