Our World Statistics Day conversations have been a great reminder of how much statistics can inform our lives. Do you have an example of how statistics has made a difference in your life? Share your story with the Community!
Choose Language Hide Translation Bar
Highlighted
Level II

## Conditional marked and excluded specified rows with multiple criteria between columns

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_Classify Box BinPos Slot iClass BinClass_ BinClass Charge 10812_3 19 81 19 20.2 Class 20.2 RUN#7002 10812_3 19 82 19 20.2 Class 20.2 RUN#7002 10812_3 19 83 19 20.2 Class 20.2 RUN#7002 10812_3 19 84 19 20.2 Class 20.2 RUN#7002 10812_3 19 85 19 20.2 Class 20.2 RUN#7002 10812_3 19 86 19 20.2 Class 20.2 RUN#7002 10812_3 19 87 19 20.2 Class 20.2 RUN#7002 10812_3 19 88 19 20.2 Class 20.2 RUN#7002 10812_3 19 89 19 20.2 Class 20.2 RUN#7002 o \N 19 20.2 Class 20.2 RUN#7002 o \N 19 20.2 Class 20.2 RUN#7002 o \N 19 20.2 Class 20.2 RUN#7002 o \N 19 20.2 Class 20.2 RUN#7002 o \N 19 20.2 Class 20.2 RUN#7002 o \N 19 20.2 Class 20.2 RUN#7002 o \N 19 20.2 Class 20.2 RUN#7002 o \N 19 20.2 Class 20.2 RUN#7002 o \N 19 20.2 Class 20.2 RUN#7002 o \N 19 20.2 Class 20.2 RUN#7002 10812_3 19 100 19 20.2 Class 20.2 RUN#7002 10816_3 19 1 19 20.2 Class 20.2 RUN#7002 10816_3 19 2 19 20.2 Class 20.2 RUN#7002 10816_3 19 3 19 20.2 Class 20.2 RUN#7002 10816_3 19 4 19 20.2 Class 20.2 RUN#7002 10816_3 19 5 19 20.2 Class 20.2 RUN#7002 10816_3 19 6 19 20.2 Class 20.2 RUN#7002 10816_3 19 7 19 20.2 Class 20.2 RUN#7002 x \N 19 20.2 Class 20.2 RUN#7002 10816_3 19 8 19 20.2 Class 20.2 RUN#7002 10816_3 19 9 19 20.2 Class 20.2 RUN#7002 10816_3 19 10 19 20.2 Class 20.2 RUN#7002 10816_3 19 11 19 20.2 Class 20.2 RUN#7002 x \N 19 20.2 Class 20.2 RUN#7002 10816_3 19 12 19 20.2 Class 20.2 RUN#7002 10816_3 19 13 19 20.2 Class 20.2 RUN#7002 10816_3 19 14 19 20.2 Class 20.2 RUN#7002 x \N 19 20.2 Class 20.2 RUN#7002 10816_3 19 15 19 20.2 Class 20.2 RUN#7002 10816_3 19 16 19 20.2 Class 20.2 RUN#7002 x \N 19 20.2 Class 20.2 RUN#7002 10816_3 19 17 19 20.2 Class 20.2 RUN#7002 10816_3 19 18 19 20.2 Class 20.2 RUN#7002 10816_3 19 19 19 20.2 Class 20.2 RUN#7002 10816_3 19 20 19 20.2 Class 20.2 RUN#7001 10816_3 19 21 19 20.2 Class 20.2 RUN#7001 10816_3 19 22 19 20.2 Class 20.2 RUN#7001

 Cell_Classify Box BinPos Slot iClass BinClass_ BinClass Charge 10812_3 19 81 19 20.2 Class 20.2 RUN#7002 10812_3 19 82 19 20.2 Class 20.2 RUN#7002 10812_3 19 83 19 20.2 Class 20.2 RUN#7002 10812_3 19 84 19 20.2 Class 20.2 RUN#7002 10812_3 19 85 19 20.2 Class 20.2 RUN#7002 10812_3 19 86 19 20.2 Class 20.2 RUN#7002 10812_3 19 87 19 20.2 Class 20.2 RUN#7002 10812_3 19 88 19 20.2 Class 20.2 RUN#7002 10812_3 19 89 19 20.2 Class 20.2 RUN#7002 o \N 19 20.2 Class 20.2 RUN#7002 o \N 19 20.2 Class 20.2 RUN#7002 o \N 19 20.2 Class 20.2 RUN#7002 o \N 19 20.2 Class 20.2 RUN#7002 o \N 19 20.2 Class 20.2 RUN#7002 o \N 19 20.2 Class 20.2 RUN#7002 o \N 19 20.2 Class 20.2 RUN#7002 o \N 19 20.2 Class 20.2 RUN#7002 o \N 19 20.2 Class 20.2 RUN#7002 o \N 19 20.2 Class 20.2 RUN#7002 o \N 19 20.2 Class 20.2 RUN#7002 o \N 19 20.2 Class 20.2 RUN#7002 o \N 19 20.2 Class 20.2 RUN#7002 o \N 19 20.2 Class 20.2 RUN#7002 10816_3 19 4 19 20.2 Class 20.2 RUN#700
Article Labels

There are no labels assigned to this post.