Choose Language Hide Translation Bar

Community Trekker

Joined:

Feb 9, 2015

## Running Count of Repeated Values JSL Script-Part2

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

colorcount_colorShiftBox
..........
RED4NIGHTRED-4
RED5NIGHTRED-4
RED1DAYRED-1
RED2DAYRED-1
BLUE1NIGHTBLUE-3
YELLOW1DAYYELLOW-2
BLUE2NIGHTBLUE-3
GREEN1NIGHTGREEN-2
RED3DAYRED-1
BLUE1DAYBLUE-1
GREEN1NIGHTGREEN-2
WHITE1NIGHT WHITE-1
WHITE1DAY WHITE-1
RED4DAYRED-1
RED5DAYRED-1
RED1DAY RED-2
BLUE3NIGHTBLUE-3
YELLOW2DAYYELLOW-2
RED1NIGHTRED-1
1 ACCEPTED SOLUTION

Accepted Solutions

Super User

Joined:

Mar 17, 2015

Solution

## Re: Running Count of Repeated Values JSL Script-Part2

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

5

)

*Edit* or

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

6 REPLIES 6

Super User

Joined:

Mar 17, 2015

Solution

## Re: Running Count of Repeated Values JSL Script-Part2

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

5

)

*Edit* or

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

Super User

Joined:

Jun 23, 2011

## Re: Running Count of Repeated Values JSL Script-Part2

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

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

Community Trekker

Joined:

Feb 9, 2015

## Re: Running Count of Repeated Values JSL Script-Part2

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

@

Super User

Joined:

Jun 23, 2011

## Re: Running Count of Repeated Values JSL Script-Part2

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

);

Community Trekker

Joined:

Feb 9, 2015

## Re: Running Count of Repeated Values JSL Script-Part2

Thanks, MS.

Community Trekker

Joined:

Feb 9, 2015

## Re: Running Count of Repeated Values JSL Script-Part2

Thanks, it work out great!