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
Best answer by ryangifford
View originalYou 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)
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.