Forum Discussion

DDON's avatar
DDON
Frequent Visitor
5 years ago
Solved

Data maniplulation after grouping

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!

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

     

    try using this M-expression

     

     

     

     

    then expand column records:

     

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

     

     

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

     

    • DDON's avatar
      DDON
      Frequent Visitor
      5 years ago

      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's avatar
        Anonymous
        Not applicable
        5 years ago

         

        try using this M-expression

         

         

         

         

        then expand column records:

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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