Data maniplulation after grouping

[DDON]

Happy Friday!

I am trying to extract data specific for each group from one column in a table based on finding the maximum value in another column.

I have the following setup

Key = column by which data is to be grouped

Reference = The column I wish to use to identify/manipulate data

Value = The values I want to output

Scenario 1

I wish to find the corresponding value for the maximum reference within a group:

Scenario 2

(I may be pushing my luck here!)

I wish use the numbers in the Reference column to perform an operation upon the value column - here it is Average(Reference x Value). I am less fussed about the exact formulation here but the method I can apply

I would be hugely greatful for any ideas/solutions

Thanks!

[Anonymous]

you should group by on key using UI (for example) in this way:

[DDON]

Thanks for your reply, as far as I can tell the Group GUI only allows operations on one column. In scenario 1 I am not really interested in the max of R but which V is on the same row as th max of R.

Sorry if I am missing something in your reply

[Anonymous · Accepted solution]

try using this M-expression

then expand column records:

[DDON]

Perfect. Thanks for your time on that. I think I understand the syntax of the solution apart from the "_". If you have a moment could you explain what that is up to or point me towards the documentation.

Thanks again

[Anonymous]

You can just add a MAX operation on the Value column right from the Group function GUI. Also add an All Rows column. That should give you what you are looking for. You'll have what you need to then add the average column.

--Nate