This is the log table. I’d like to have a MAX of [Total] at the level of [Lookup Grade].[Lookup Type], and to put this in my User table.
For example, for user Charlie, the MAX of Grade=1 and Type=6 Count is 200, while for Max it is 275. For Charlie, the MAX of Grade=1 and Type=Navy Seal is 67.
In a spreadsheet I’d use a pivot table and in SQL I’d use GROUP BY. Is there a way to do this conditional / segmented aggregation in the Glide data table?
Sounds like you’ll need to create a Template column in both tables that joins the user, Grade, and Type together. Then you can use a relation that links those Template values in both tables together, and your Rollup can use the relation bto find the MAX for that specific combination.
@Robert_Petitto - thanks for the reply, and good question! I’d just omitted those columns in my screenshot so that the image was simpler, but there is a User ID field in there:
Just to build on your suggestion, using your approach is it also right (as @Jeff_Hager described) that I would need to create a Template field also in the Log table for the relationship? Might be a stupid question, am just in the early days with Glide and appreciate the help!
Relations look for exact matches between the column selected in the source table (Users) and the column selected in the destination table (Log). So, yes, you would need a template column in both tables, and the result of those template columns need to be a value that will match in both tables.
