Solved

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

• 26 views

• New Member
• 0 replies

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

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) ``