nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Running Count of Repeated Values JSL Script-Part2

bernie426

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

Accepted answers

vince_faller

Building 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 ),

)

*Edit* or

Mod( Eval( Eval Expr( Col Sum( Row() <= Expr( Row() ), :Color, :Shift ) ) ) - 1, 5 ) + 1

All comments

vince_faller

Building 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 ),

)

*Edit* or

Mod( Eval( Eval Expr( Col Sum( Row() <= Expr( Row() ), :Color, :Shift ) ) ) - 1, 5 ) + 1

Great formula! I think this would work for the Box:

Eval(Eval Expr(:color || "-" || Char(Ceiling(Col Sum(Row() <= Expr(Row()), :color,:Shift) / 5))))

bernie426

Hi 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<<New Column( "MS_Box_Script",

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

Color Boxing_Example.jmp

bernie426

Thanks, it work out great!

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

);