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. ID1MAIN_RADSUB_RADCOUNT1M101001796M101011583M101021521M101031621M101101601M101111539M101121639M101131610M105001601M105011539M105021639M105031610M105101856M105111960M105121931M105132004M110001993M110011779M110021820M110031813M110101813M110111736M110121620M110131643 Thanks a lot.

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: ID1MAIN_RADSUB_RADCOUNT1 M101001796MaxM101011583 M101021521 M101031621 M101101601 M101111539 M101121639MaxM101131610 M105001601 M105011539 M105021639MaxM105031610 M105101856 M105111960 M105121931 M105132004MaxM110001993MaxM110011779 M110021820 M110031813 M110101813MaxM110111736 M110121620 M110131643

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Finding the max value as per ID

adam

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.

Accepted answers

adam

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

txnelson

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

All comments

txnelson

The Summary Platform is designed to do this task

Tables==>Summary

adam

Hi Jim,

Thanks.

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

txnelson

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?

adam

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.