Subscribe Bookmark RSS Feed

Running Count of Repeated Values JSL Script-Part2

bernie426

Community Trekker

Joined:

Feb 9, 2015

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
vince_faller

Super User

Joined:

Mar 17, 2015

Solution

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
vince_faller

Super User

Joined:

Mar 17, 2015

Solution

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

ms

Super User

Joined:

Jun 23, 2011

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

Community Trekker

Joined:

Feb 9, 2015

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,

ColorShiftExcepted Box ResultMS_Box_Script
BLUEDAYBLUE-1BLUE-1
BLUEDAYBLUE-1BLUE-1
BLUEDAYBLUE-1BLUE-1
BLUENIGHTBLUE-1BLUE-1
BLUENIGHTBLUE-1BLUE-1
BLUENIGHTBLUE-1BLUE-1
BLUENIGHTBLUE-1BLUE-1
GREENDAYGREEN-1GREEN-1
GREENDAYGREEN-1GREEN-1
GREENDAYGREEN-1GREEN-1
GREENDAYGREEN-1GREEN-1
GREENDAYGREEN-1GREEN-1
GREENDAYGREEN-2GREEN-1
GREENDAYGREEN-2GREEN-1
GREENDAYGREEN-2GREEN-1
GREENNIGHTGREEN-1GREEN-1
REDDAYRED-1RED-1
REDDAYRED-1RED-1
REDDAYRED-1RED-1
REDDAYRED-1RED-1
REDDAYRED-1RED-1
REDDAYRED-2RED-1
REDDAYRED-2RED-1
REDDAYRED-2RED-1
REDNIGHTRED-1RED-1
REDNIGHTRED-1RED-1
REDNIGHTRED-1RED-1
REDNIGHTRED-1RED-1
REDNIGHTRED-1RED-1
YELLOWDAYYELLOW-1YELLOW-1
YELLOWDAYYELLOW-1YELLOW-1
YELLOWDAYYELLOW-1YELLOW-1
YELLOWNIGHTYELLOW-1YELLOW-1
YELLOWNIGHTYELLOW-1YELLOW-1
YELLOWNIGHTYELLOW-1YELLOW-1
YELLOWNIGHTYELLOW-1YELLOW-1
YELLOWNIGHTYELLOW-1YELLOW-1
YELLOWNIGHTYELLOW-2YELLOW-1
YELLOWNIGHTYELLOW-2YELLOW-1

@

ms

Super User

Joined:

Jun 23, 2011

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

);


bernie426

Community Trekker

Joined:

Feb 9, 2015

Thanks, MS.

bernie426

Community Trekker

Joined:

Feb 9, 2015

Thanks, it work out great!