Solved

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

  • 10 May 2022
  • 1 reply
  • 26 views

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

icon

Best answer by ryangifford 10 May 2022, 18:27

View original

1 reply

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) 

 

Reply