Subscribe Bookmark RSS Feed

Select where by column condition

bernie426

Community Trekker

Joined:

Feb 9, 2015

I am struggling to find out a way to exclude certain rows using column condition.

As an example, a summary table listed below, the first  5 columns are the group conditions that I use to get summary table.

I have to manually select and exclude the highlighted blue font row, because this Box is still in the machine and have not get to a full count of 100 as Max(Slot) showed.

Then this Box will continue serving for the next operation shift start and take out when it reach 100. So the highlighted box cannot be counted as a output for day.

Is there a way to select the row where the Max(dCreated) is largest # by putting  Class, and Day column into group? Actually the table have have more than 100,000 of row and it is troublesome to do it manually.

Thanks in advance,

Date_PROD.ClassLane_NumberShiftBoxN RowsMin(dCreated)Max(dCreated)Max(Slot)
2016-09-19Bin3LANE 2Day77786_24409/19/2016 7:21:13 AM09/19/2016 7:40:07 AM100
2016-09-19Bin3LANE 2Day77808_210009/19/2016 7:40:12 AM09/19/2016 8:00:23 AM100
2016-09-19Bin3LANE 2Day77813_210009/19/2016 8:00:29 AM09/19/2016 4:31:46 PM100
2016-09-19Bin3LANE 2Day77860_25609/19/2016 4:31:48 PM09/19/2016 6:26:08 PM56
2016-09-19Bin3LANE 2Night77860_24409/19/2016 7:04:49 PM09/20/2016 1:35:04 AM100
1 ACCEPTED SOLUTION

Accepted Solutions
Solution

Here is an example that I think will do what you want......provided that I have correctly interpreted your needs:

Names Default To Here( 1 );

dt = Current Data Table();

dt << select where( :Max( dCreated ) == Col Maximum( :Max( dCreated ), Class, Shift ) );

dt << Exclude();

Jim
2 REPLIES
Solution

Here is an example that I think will do what you want......provided that I have correctly interpreted your needs:

Names Default To Here( 1 );

dt = Current Data Table();

dt << select where( :Max( dCreated ) == Col Maximum( :Max( dCreated ), Class, Shift ) );

dt << Exclude();

Jim
bernie426

Community Trekker

Joined:

Feb 9, 2015

Thanks, Jim.

I used the col max in formula but not able to get select where to work out.

Great solution!