Select where by column condition

bernie426 — Tue, 20 Sep 2016 16:35:34 GMT

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.	Class	Lane_Number	Shift	Box	N Rows	Min(dCreated)	Max(dCreated)	Max(Slot)
2016-09-19	Bin3	LANE 2	Day	77786_2	44	09/19/2016 7:21:13 AM	09/19/2016 7:40:07 AM	100
2016-09-19	Bin3	LANE 2	Day	77808_2	100	09/19/2016 7:40:12 AM	09/19/2016 8:00:23 AM	100
2016-09-19	Bin3	LANE 2	Day	77813_2	100	09/19/2016 8:00:29 AM	09/19/2016 4:31:46 PM	100
2016-09-19	Bin3	LANE 2	Day	77860_2	56	09/19/2016 4:31:48 PM	09/19/2016 6:26:08 PM	56
2016-09-19	Bin3	LANE 2	Night	77860_2	44	09/19/2016 7:04:49 PM	09/20/2016 1:35:04 AM	100

Re: Select where by column condition

txnelson — Wed, 21 Sep 2016 03:49:22 GMT

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();

Re: Select where by column condition

bernie426 — Wed, 21 Sep 2016 16:32:23 GMT

Thanks, Jim.

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

Great solution!

topic Select where by column condition in Discussions

Select where by column condition

Re: Select where by column condition

Re: Select where by column condition