turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Discussions
- :
- Running Count of Repeated Values JSL Script-Part2

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Mar 26, 2015 9:31 PM
(3648 views)

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 |

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Mar 27, 2015 4:49 AM
(3435 views)
| Posted in reply to message from vince_faller 03/27/2015 06:49 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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 |

@

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

**)**;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Thanks, MS.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Apr 3, 2015 10:25 AM
(3435 views)
| Posted in reply to message from vince_faller 03/27/2015 06:49 AM

Thanks, it work out great!