Conditional marked and excluded specified rows with multiple criteria between columns
Jun 23, 2016 12:08 PM(1563 views)
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.