how to find maximum of a group of rows in a certain column which satisfies a condition in tibco spotfire

Question

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:

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

I want to group all the rows with [col 1] = 1, and find the max of col 2 considering only those rows that have [col 3] = "y".

My final table must look like:

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

Can some one please help me out with this?

i have created one column which finds the maximum, and then with that the actual column which i need, but i wanted to do without using that extra column, because i'm using this logic many times, so i ended up creating 40+ calculated columns. — Kural Manivannan

Monte_fisto Monte_fisto · Accepted Answer · 2016-05-02T12:53:15

First(case when [col 3]="y" then Max([col 2]) OVER ([col 1]) end) OVER ([col 1]) should do the trick (version 7.5).

Thanks!

how to find maximum of a group of rows in a certain column which satisfies a condition in tibco spotfire

3 Answers