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

Finding the maximum of a column by matching another column condition

Mikasa — Fri, 09 Jun 2023 23:37:33 GMT

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

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

txnelson — Thu, 10 Sep 2020 17:20:00 GMT

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

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

Mikasa — Thu, 10 Sep 2020 17:31:40 GMT

Thank you very much Jim :)

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

Mikasa — Thu, 10 Sep 2020 17:33:06 GMT

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

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

txnelson — Thu, 10 Sep 2020 18:17:52 GMT

I do not understand what you are asking for.

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

Mikasa — Thu, 10 Sep 2020 19:42:20 GMT

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.

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

txnelson — Thu, 10 Sep 2020 20:06:01 GMT

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

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

Mikasa — Thu, 10 Sep 2020 21:16:56 GMT

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

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

txnelson — Thu, 10 Sep 2020 21:36:15 GMT

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

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

Mikasa — Fri, 11 Sep 2020 15:19:57 GMT

You can see the data attached.

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

txnelson — Fri, 11 Sep 2020 16:02:08 GMT

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

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

Mikasa — Fri, 11 Sep 2020 16:09:44 GMT

Great solution
Thanks:)