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! Go to 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)
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)