cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
Mikasa
Level II

Finding the maximum of a column by matching another column condition

Hi every one,

I have to find the maximum value in a column (:PMA Counter) after checking the condition in another column (: Condition). the condition column has "After" and "Before" characters for the different rows. 

I want to pick those which are "After" and fin the maximum of (:PMA Counter) between them.  I already use the below script but it is not working and only give me the Max without considering the Condition column.

dt=Current table();

dt<<Select Where(Contains (:Condition ,"After" ));
dt<<New Column ("Current PMA Counter", Character,Nominal, Formula(Col Max(:PMA Counter)));

11 REPLIES 11
txnelson
Super User

Re: Finding the maximum of a column by matching another column condition

I mistakenly created the Current PMA Counter column as a character string, and it needed to be numeric.....the JSL below seems to work

names default to here(1);
dt=current data table();
dt << New Column( "Current PMA Counter",
	Formula(
		If( Row() == 1,
			x = Col Max( If( Contains( :Condition, "After" ), :PMA Counter, . ) )
		);
		If( Contains( :Condition, "After" ),
			y = x,
			y = .
		);
		y;
	)
);
Jim
Mikasa
Level II

Re: Finding the maximum of a column by matching another column condition

Great solution
Thanks:)