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 maximum of a column by matching another column condition

Mikasa

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

Accepted answers

txnelson

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

txnelson

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

All comments

txnelson

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

Mikasa

Thank you very much Jim

Mikasa

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

txnelson

I do not understand what you are asking for.

Mikasa

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.