Finding the maximum of a column by matching another column condition

[#1]

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

[#2 · Accepted solution]

The row Select is not honored by the formula.  Here is a way to handle what you need

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

[#3]

Thank you very much Jim

[#4]

Can I have the Maximum only in the rows related to "After" rows?

[#5]

I do not understand what you are asking for.

[#6]

I meant only those rows that had "After" in the Condition column get the Max value in the Current PMA Counter column. Other rows remain blank.

[#7]

Here are a couple of versions of the formula(They are not tested).  They differ in what the Col Max is based on

// formula where col max is based upon only rows with "After" in PMA Counter
// and the Col Max only appears in rows where PMA Counter has "After" in it
dt << New Column( "Current PMA Counter",
	Character,
	Nominal,
	Formula(
		If( Row() == 1,
			x = Col Max( If( Contains( :Condition, "After" ), :PMA Counter, . ) )
		);
		If( Contains( :Condition, "After" ),
			y = x,
			y = .
		);
		y;
	)
);

// formula where col max is based upon all rows
// and the Col Max only appears in rows where PMA Counter has "After" in it
dt << New Column( "Current PMA Counter",
	Character,
	Nominal,
	Formula(
		If( Row() == 1,
			x = Col Max( :PMA Counter )
		);
		If( Contains( :Condition, "After" ),
			y = x,
			y = .
		);
		y;
	)
);

[#8]

Thanks, Jim
it is finding the value but for some reason does not show up in the column. THE current PMA Counter is all blank

[#9]

can you attach a sample data table that you are applying this to?

[#10]

You can see the data attached.