Subscribe Bookmark RSS Feed

Conditional marked and excluded specified rows with multiple criteria between columns

bernie426

Community Trekker

Joined:

Feb 9, 2015

Hello,

I have a table below which is kind of messy and need to have lot of filtering before analyzing the table.

The problems is when the table be extracted from SQL server some of the rows are missing Box, BinPos and Slot information. However, some of the missing Box, BinPos and Slot is real and some are fake.

Therefore, it take me lot of time to go over a table like this to filter out the row that are real missing rows and the row that should be existed but missing Box, BinPos, and Slot.

A new column called Cell_Classify, which is a Row State column, is created make mark to distinguish this problem.

As the row marked as "o" which indicate the rows actually exist but missed column information from Box, BinPos, and Slot. The way distinguish the rows which is missing Box, BinPos and Slot information are REALLY  exist is based on the "Slot" Column. For example,  the latest row where the row start to miss Box, BinPos and Slot data have a the Box, BinPos and Slot data as (10812_3, 19, and 89) and the first row where start to show Box, BinPos, and Slot information after missing those information is having Box, BinPos, Slot data as (10812_3, 19, 100). The Slot difference between this two row minus 1 (Slot difference-1) equal the same amount of row that missed Box, BinPos and Slot information in between this two rows.

For the rows that are missing Box, BinPos, and Slot data information is really a true missing  is marked as "x" at the Cell Classify column. As one can see that the row before and after the row where have a "x" mark the Slot is consecutive increased, which mean that the the Slot difference between the row where is missing Box, BinPos, and Slot is equal to 1.

I would like to have a JSL script to help to this, which can greatly save me table filtering time.

There is some criteria to meet to design a JSL for this,

     (1) Box number will change as Slot reach 100. Slot max is 100.

     (2) BinPos have the same meaning as iClass and BinClass, but they for combined fro difference table. There are totally 24 BinPos, iClass, and BinClass. And this issue    

     happen at each iClass. the calculation should go over 24 iClass

     (3) for the same Box number if Slot difference -1 = row missing amount, then the rows where missing Box, BinPos, Slot should marked "o" at the Cell_Classify Row State column and highlighted.

     (4) for the same Box number if Slot difference-1=0 and the row missing amount=1, then the row which missed Box, BinPos, Slots is a real missing row. This row need to mark as "x" and excluded and hide.

     (5) the most difficult part I think is the rows, where is missing the Box, BinPos, and Slot, is really exist "o" but the box number change. For example, on the second table below. If this circumstance happen, then (3) criteria need to be modified.

Really appreciate for the help.

Thanks,

Cell_ClassifyBoxBinPosSlotiClassBinClass_BinClassCharge
10812_319811920.2Class 20.2RUN#7002
10812_319821920.2Class 20.2RUN#7002
10812_319831920.2Class 20.2RUN#7002
10812_319841920.2Class 20.2RUN#7002
10812_319851920.2Class 20.2RUN#7002
10812_319861920.2Class 20.2RUN#7002
10812_319871920.2Class 20.2RUN#7002
10812_319881920.2Class 20.2RUN#7002
10812_319891920.2Class 20.2RUN#7002
o\N1920.2Class 20.2RUN#7002
o\N1920.2Class 20.2RUN#7002
o\N1920.2Class 20.2RUN#7002
o\N1920.2Class 20.2RUN#7002
o\N1920.2Class 20.2RUN#7002
o\N1920.2Class 20.2RUN#7002
o\N1920.2Class 20.2RUN#7002
o\N1920.2Class 20.2RUN#7002
o\N1920.2Class 20.2RUN#7002
o\N1920.2Class 20.2RUN#7002
10812_3191001920.2Class 20.2RUN#7002
10816_31911920.2Class 20.2RUN#7002
10816_31921920.2Class 20.2RUN#7002
10816_31931920.2Class 20.2RUN#7002
10816_31941920.2Class 20.2RUN#7002
10816_31951920.2Class 20.2RUN#7002
10816_31961920.2Class 20.2RUN#7002
10816_31971920.2Class 20.2RUN#7002
x\N1920.2Class 20.2RUN#7002
10816_31981920.2Class 20.2RUN#7002
10816_31991920.2Class 20.2RUN#7002
10816_319101920.2Class 20.2RUN#7002
10816_319111920.2Class 20.2RUN#7002
x\N1920.2Class 20.2RUN#7002
10816_319121920.2Class 20.2RUN#7002
10816_319131920.2Class 20.2RUN#7002
10816_319141920.2Class 20.2RUN#7002
x\N1920.2Class 20.2RUN#7002
10816_319151920.2Class 20.2RUN#7002
10816_319161920.2Class 20.2RUN#7002
x\N1920.2Class 20.2RUN#7002
10816_319171920.2Class 20.2RUN#7002
10816_319181920.2Class 20.2RUN#7002
10816_319191920.2Class 20.2RUN#7002
10816_319201920.2Class 20.2RUN#7001
10816_319211920.2Class 20.2RUN#7001
10816_319221920.2Class 20.2RUN#7001

Cell_ClassifyBoxBinPosSlotiClassBinClass_BinClassCharge
10812_319811920.2Class 20.2RUN#7002
10812_319821920.2Class 20.2RUN#7002
10812_319831920.2Class 20.2RUN#7002
10812_319841920.2Class 20.2RUN#7002
10812_319851920.2Class 20.2RUN#7002
10812_319861920.2Class 20.2RUN#7002
10812_319871920.2Class 20.2RUN#7002
10812_319881920.2Class 20.2RUN#7002
10812_319891920.2Class 20.2RUN#7002
o\N1920.2Class 20.2RUN#7002
o\N1920.2Class 20.2RUN#7002
o\N1920.2Class 20.2RUN#7002
o\N1920.2Class 20.2RUN#7002
o\N1920.2Class 20.2RUN#7002
o\N1920.2Class 20.2RUN#7002
o\N1920.2Class 20.2RUN#7002
o\N1920.2Class 20.2RUN#7002
o\N1920.2Class 20.2RUN#7002
o\N1920.2Class 20.2RUN#7002
o\N1920.2Class 20.2RUN#7002
o\N1920.2Class 20.2RUN#7002
o\N1920.2Class 20.2RUN#7002
o\N1920.2Class 20.2RUN#7002
10816_31941920.2Class 20.2RUN#700