topic Re: Loop to find max value for each unique values in Discussions

Loop to find max value for each unique values

Jackie_ — Tue, 12 Dec 2023 17:37:34 GMT

Hi,

I am trying to iterate over each values of X and Y and save the max rn value in the MaxValue column.

Here is what I tried but something doesn't seem correct. Any advice?

Names Default To Here( 1 );

dt = Current Data Table();

dt << begin data table();

// Loop through rows
For( i = 1, i <= N Rows( dt ), i++,
	currentBarcode = dt:Barcode[i];
   // Find the location of the maximum rn value for the current Barcode
	maxRNRow = Max( dt:rn[dt:Barcode[i] == currentBarcode] );
   // Check if the maxRNRow is not 0 (meaning there is at least one matching Barcode)
	If( maxRNRow != 0, 
       // Update "MaxValue" column with the max rn value for the current Barcode
		dt:MaxValue[i] = dt:rn[maxRNRow]
	);
);
dt << end data table();

Re: Loop to find max value for each unique values

mmarchandTSI — Tue, 12 Dec 2023 17:47:00 GMT

Try this instead.

Col Max( :rn, :Y, :X )

edit

To be clearer, set the formula for :MaxValue to

Col Max( :rn, :Barcode )

Re: Loop to find max value for each unique values

jthi — Tue, 12 Dec 2023 18:37:51 GMT

No loops needed here. You can do what @mmarchandTSI suggested and use Col Max with byvar, you could use Summarize to calculate the maximum values and then create new column using those or you could create summary table and join (or rather update) that to your table with the max value. Also the the begin/end messages are

dt << Begin Data Update;
dt << End Data Update;

Re: Loop to find max value for each unique values

Jackie_ — Thu, 14 Dec 2023 18:10:29 GMT

@mmarchandTSI Col max works only with :Barcode column. The issue is some table don't have Barcode column so am trying to see if I can use X and Y Coordinates to assign the max rn value in

Re: Loop to find max value for each unique values

jthi — Thu, 14 Dec 2023 18:32:05 GMT

You can add more than one byvar

Names Default To Here(1);
dt = Open("$SAMPLE_DATA/Big Class.jmp");
dt << New Column("Maximum Value for Each Age and Sex Group",
	Formula(Col Maximum(:height, :age, :sex))
);

Col Max()

Re: Loop to find max value for each unique values

mmarchandTSI — Thu, 14 Dec 2023 18:59:24 GMT

Right, so the first formula I posted will work for what you're doing, as @jthi alluded to.