How to return column A value based on largest column B value

I am trying to create a calculated field/measure that returns the string value in column C, based on the largest numeric value in column B for each ID (column A)

Example Source
ID | # | String
101 | 5 | Banana
101 | 8 | Blueberry
101 | 3 | Apple
102 | 9 | Peach

Desired Return
101 | Blueberry
102 | Peach

Solved Solved
0 1 308
1 ACCEPTED SOLUTION

You could do this with table calcs and just hide some rows from the visualization. It might be easier with a group statement in the Lookml but here’s the idea:

row_id_calc

if(match(${ID},${ID})=offset(match(${ID},${ID}),-1)
 , 1+row()-match(${ID},${ID})
 , 1)

max_calc

 max(offset_list(${#},-(${row_id_calc}-1),${row_id_calc}))

Display String:

if(${max_calc} = ${#}, ${String}, null)

Hide extra rows:

if(

     if(${max_calc} = ${#}, ${#}, null) != coalesce(null, 0),

      yes, no) 

View solution in original post

1 REPLY 1

You could do this with table calcs and just hide some rows from the visualization. It might be easier with a group statement in the Lookml but here’s the idea:

row_id_calc

if(match(${ID},${ID})=offset(match(${ID},${ID}),-1)
 , 1+row()-match(${ID},${ID})
 , 1)

max_calc

 max(offset_list(${#},-(${row_id_calc}-1),${row_id_calc}))

Display String:

if(${max_calc} = ${#}, ${String}, null)

Hide extra rows:

if(

     if(${max_calc} = ${#}, ${#}, null) != coalesce(null, 0),

      yes, no) 
Top Labels in this Space
Top Solution Authors