Choose Language Hide Translation Bar
adam
Community Trekker

Finding the max value as per ID

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_RADCOUNT1
M101001796
M101011583
M101021521
M101031621
M101101601
M101111539
M101121639
M101131610
M105001601
M105011539
M105021639
M105031610
M105101856
M105111960
M105121931
M105132004
M110001993
M110011779
M110021820
M110031813
M110101813
M110111736
M110121620
M110131643

 

Thanks a lot.

 

0 Kudos
2 ACCEPTED SOLUTIONS

Accepted Solutions
adam
Community Trekker

Re: Finding the max value as per ID

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 
M101001796Max
M101011583 
M101021521 
M101031621 
M101101601 
M101111539 
M101121639Max
M101131610 
M105001601 
M105011539 
M105021639Max
M105031610 
M105101856 
M105111960 
M105121931 
M105132004Max
M110001993Max
M110011779 
M110021820 
M110031813 
M110101813Max
M110111736 
M110121620 
M110131643 

View solution in original post

0 Kudos
txnelson
Super User

Re: Finding the max value as per ID

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

View solution in original post

0 Kudos
8 REPLIES 8
txnelson
Super User

Re: Finding the max value as per ID

The Summary Platform is designed to do this task

     Tables==>Summary

 

Jim
0 Kudos
adam
Community Trekker

Re: Finding the max value as per ID

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 
M101001796Max
M101011583 
M101021521 
M101031621 
M101101601 
M101111539 
M101121639Max
M101131610 
M105001601 
M105011539 
M105021639Max
M105031610 
M105101856 
M105111960 
M105121931 
M105132004Max
M110001993Max
M110011779 
M110021820 
M110031813 
M110101813Max
M110111736 
M110121620 
M110131643 

View solution in original post

0 Kudos
txnelson
Super User

Re: Finding the max value as per ID

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?

Jim
0 Kudos
adam
Community Trekker

Re: Finding the max value as per ID

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.


0 Kudos
txnelson
Super User

Re: Finding the max value as per ID

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

max.PNG

Jim
0 Kudos
adam
Community Trekker

Re: Finding the max value as per ID

 

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. 

 

ID1MAIN_RADSUB_RADCOUNT1Maximum
M1010017961796
M101011583 
M101021521 
M101031621 
M101101601 
M101111539 
M1011216391639
M101131610 
M105001601 
M105011539 
M1050216391639
M105031610 
M105101856 
M105111960 
M105121931 
M1051320042004
M1100019931993
M110011779 
M110021820 
M110031813 
M1101018131813
M110111736 
M110121620 
M110131643 

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

 

Thanks. Appreciate that.

0 Kudos
txnelson
Super User

Re: Finding the max value as per ID

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

View solution in original post

0 Kudos
adam
Community Trekker

Re: Finding the max value as per ID

Awesome Jim !!

0 Kudos