title: how to find maximum of a group of rows in a certain column which satisfies a condition in tibco spotfire
body: <p>I want to find maximum of a group of rows in a certain column which satisfies a condition in TIBCO Spotfire. For example, consider the table below:</p>
  
  <pre>
  col 1|col 2|col 3
    1  |  2  |  y  
    1  |  3  |  y  
    1  |  6  |  y  
    1  |  8  |  n  
    1  |  7  |  n  
    1  |  6  |  y  
    2  |  2  |  y  
    2  |  10 |  y  
    2  |  6  |  y  
    2  |  9  |  n  
    2  |  7  |  y  
    2  |  6  |  n  
  </pre>
  
  <p>I want to group all the rows with <code>[col 1] = 1</code>, and find the max of col 2 considering only those rows that have <code>[col 3] = "y"</code>.</p>
  
  <p>My final table must look like:</p>
  
  <pre>
  col 1|col 2|col 3|col 4
    1  |  2  |  y  | 6
    1  |  3  |  y  | 6
    1  |  6  |  y  | 6
    1  |  8  |  n  | 6
    1  |  7  |  n  | 6
    1  |  6  |  y  | 6
    2  |  2  |  y  | 10
    2  |  10 |  y  | 10
    2  |  6  |  y  | 10
    2  |  9  |  n  | 10
    2  |  7  |  y  | 10
    2  |  6  |  n  | 10
  </pre>
  
  <p>Can some one please help me out with this?</p>