topic Re: Finding the max value as per ID in Discussions

Finding the max value as per ID

adam — Tue, 06 Mar 2018 07:06:02 GMT

Hi,

I have the following table as shown. My intention is to find the max(or min) value in the COUNT1 column per each ID1 and populate them in a new table which will have similar column as original table. Can anyone suggest what is the best way to do it.

ID1	MAIN_RAD	SUB_RAD	COUNT1
M101	0	0	1796
M101	0	1	1583
M101	0	2	1521
M101	0	3	1621
M101	1	0	1601
M101	1	1	1539
M101	1	2	1639
M101	1	3	1610
M105	0	0	1601
M105	0	1	1539
M105	0	2	1639
M105	0	3	1610
M105	1	0	1856
M105	1	1	1960
M105	1	2	1931
M105	1	3	2004
M110	0	0	1993
M110	0	1	1779
M110	0	2	1820
M110	0	3	1813
M110	1	0	1813
M110	1	1	1736
M110	1	2	1620
M110	1	3	1643

Thanks a lot.

Re: Finding the max value as per ID

txnelson — Tue, 06 Mar 2018 07:15:27 GMT

The Summary Platform is designed to do this task

Tables==>Summary

Re: Finding the max value as per ID

adam — Tue, 06 Mar 2018 07:39:59 GMT

Hi Jim,

Thanks.

Yes, I tried it before. Sorry, perhaps I didn't described it in detail previously. In this case from each ID1 and Main_Rad, I would like to find the max value in COUNT column and copy(ID1, Main_Rad, Count1) to another table. It shall be similar as below:

ID1	MAIN_RAD	SUB_RAD	COUNT1
M101	0	0	1796	Max
M101	0	1	1583
M101	0	2	1521
M101	0	3	1621
M101	1	0	1601
M101	1	1	1539
M101	1	2	1639	Max
M101	1	3	1610
M105	0	0	1601
M105	0	1	1539
M105	0	2	1639	Max
M105	0	3	1610
M105	1	0	1856
M105	1	1	1960
M105	1	2	1931
M105	1	3	2004	Max
M110	0	0	1993	Max
M110	0	1	1779
M110	0	2	1820
M110	0	3	1813
M110	1	0	1813	Max
M110	1	1	1736
M110	1	2	1620
M110	1	3	1643

Re: Finding the max value as per ID

txnelson — Tue, 06 Mar 2018 13:37:31 GMT

Your second example is confusing to me. I now understand that you do not want just a summary table, but want to have the original data in it's raw form, in the new data table. But what is confusing is the placement of your MAX values in the second table. If you want the MAX for each ID1 grouping, why is there a MAX value on row 1 and row 7. Both of those rows have the same value "M101" for the ID1 column. Could you please clarify?

Re: Finding the max value as per ID

adam — Tue, 06 Mar 2018 14:08:29 GMT

Hi Jim,
Sorry for the confusion.
The ID1 group can be referred as SerialNo. Assuming that for M101(and M105
and M110 as well), it has 2 Main Radius(MAIN_RAD), 0 and 1. Then further
sub divided(ie. SUB_RAD) into 0, 1, 2 and 3. Each SUB_RAD will have its
count recorded.

In this case, I would like to get the max COUNT1 value of M101 by MAIN_RAD.
Row1 as shown in the table is the max COUNT1 value for MAIN_RAD 0 while
Row7 is for MAIN_RAD 1.

The expected outcome will choose and show the max COUNT1 value only in a
table that's similar to origin(with columns ID1, MAIN_RAD, SUB_RAD and
COUNT1). Meaning that, instead of 8 rows, the new table will have only 2
rows now.

Hope it's clearer now :)

Thanks.

Re: Finding the max value as per ID

txnelson — Tue, 06 Mar 2018 14:24:57 GMT

I think this might be what you want. I used a formula to do the calculation, but if what I did is correct, there are several other ways to do it.

If( Lag( :ID1 ) == :ID1 & Lag( :MAIN_RAD ) == :MAIN_RAD,
	.,
	Col Max( :COUNT1, :ID1, :MAIN_RAD )
)

Re: Finding the max value as per ID

adam — Wed, 07 Mar 2018 01:21:30 GMT

Thanks Jim. That works too! It will be great if I can get something like table below meaning the max value in col Maximun is in the same row as column Count1 max value.

ID1	MAIN_RAD	SUB_RAD	COUNT1	Maximum
M101	0	0	1796	1796
M101	0	1	1583
M101	0	2	1521
M101	0	3	1621
M101	1	0	1601
M101	1	1	1539
M101	1	2	1639	1639
M101	1	3	1610
M105	0	0	1601
M105	0	1	1539
M105	0	2	1639	1639
M105	0	3	1610
M105	1	0	1856
M105	1	1	1960
M105	1	2	1931
M105	1	3	2004	2004
M110	0	0	1993	1993
M110	0	1	1779
M110	0	2	1820
M110	0	3	1813
M110	1	0	1813	1813
M110	1	1	1736
M110	1	2	1620
M110	1	3	1643

BTW, I will be interested to know what will be the several ways to do it :)

Thanks. Appreciate that.

Re: Finding the max value as per ID

txnelson — Wed, 07 Mar 2018 02:13:25 GMT

Here are 3 of the ways to do this.......other examples could be thought up

Names Default To Here( 1 );
dt = New Table( "Sample",
	Add Rows( 24 ),
	New Column( "ID1",
		Character,
		"Nominal",
		Set Values(
			{"M101", "M101", "M101", "M101", "M101", "M101", "M101", "M101", "M105", "M105", "M105", "M105", "M105",
			"M105", "M105", "M105", "M110", "M110", "M110", "M110", "M110", "M110", "M110", "M110"}
		)
	),
	New Column( "MAIN_RAD",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [0, 0, 0, 0, 1, 1, 1, 1, 0, 0, 0, 0, 1, 1, 1, 1, 0, 0, 0, 0, 1, 1, 1, 1] )
	),
	New Column( "SUB_RAD",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [0, 1, 2, 3, 0, 1, 2, 3, 0, 1, 2, 3, 0, 1, 2, 3, 0, 1, 2, 3, 0, 1, 2, 3] )
	),
	New Column( "COUNT1",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values(
			[1796, 1583, 1521, 1621, 1601, 1539, 1639, 1610, 1601, 1539, 1639, 1610, 1856, 1960, 1931, 2004, 1993, 1779,
			1820, 1813, 1813, 1736, 1620, 1643]
		)
	)
);

// Create the column using a formula
dt << New Column( "By Formula",
	Character( 16 ),
	"Nominal",
	Formula(
		If( Row() == 1, flag = 0 );
		If( Lag( :ID1 ) != :ID1 | Lag( :MAIN_RAD ) != :MAIN_RAD,
			flag = 0
		);
		If( flag == 0 & Col Max( :COUNT1, :ID1, :MAIN_RAD ) == :COUNT1,
			Flag = 1;
			"Max";
		,
			""
		);
	)
);

// Create the column using JSL
dt << New Column( "Using JSL", character );
For( i = 1, i <= N Rows( dt ), i++,
	rowID1 = dt:ID1[i];
	rowMain_Rad = dt:MAIN_RAD[i];
	sameRows = dt << get rows where( dt:ID1 == rowID1 & dt:MAIN_RAD == rowMain_Rad );
	If( Max( dt:Count1[sameRows] ) == :COUNT1[i],
		dt:Using JSL[i] = "Max"
	);
);

// Create the column using Platforms
dtMax = dt << Summary(
	invisible,
	Group( :ID1, :MAIN_RAD ),
	Max( :COUNT1 ),
	Freq( "None" ),
	Weight( "None" ),
	statistics column name format( "column" ),
	Link to original data table( 0 )
);
dtMax << delete columns( "N Rows" );
dtMax << New Column( "Using Platform", character, set each value( "Max" ) );
dt << Update( With( dtMax ), Match Columns( :ID1 = :ID1, :MAIN_RAD = :MAIN_RAD, :COUNT1 = :COUNT1 ) );

Re: Finding the max value as per ID

adam — Wed, 07 Mar 2018 02:37:17 GMT

Awesome Jim !!