708421_en-US

liliconcom

Loop to find max value for each unique values

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 );
// Assuming your data table is named 'dt'
dt = Current Data Table();
// Create a new column "MaxValue" and initialize with a large negative number

// Loop through unique Barcode values

// Loop through rows
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();

 

Automation and ScriptingRe: Loop to find max value for each unique values

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

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

@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

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

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