10 Replies Latest reply: Jun 20, 2017 1:59 PM by Ben VanderLaan RSS

    Grouping by max value of a field in relation to another field

    Jacob Zelasko

      Hi,

       

      I have a sheet that's essentially laid out as follows:

       

      [JobCode]     [WorkCenter]     [Run Number]     [Time]     [Qty]

      1                  abc                   1                       .27          20

      1                  abc                   2                       .27          20

      1                  def                    3                       .27          20

      1                  def                    4                       .27          20

      2                  abc                   1                       .52          35

      2                  abc                   2                       .52          35

      2                  def                    3                       .52          35

      2                  def                    4                       .52          35

      2                  ghi                    5                       .52          35

      2                  ghi                    6                       .52          35

       

      I'm trying to figure out how to remove the duplicate values by only taking the maximum Run Number for each Job Code. So it should look something like this:

       

       

      [JobCode]     [WorkCenter]     [Run Number]     [Time]     [Qty]

      1                  def                    4                       .27          20

      2                  ghi                    6                       .52          35

       

      It should also be noted that the Time and Qty fields are already the sum of all the run numbers' time and quantity values in each job code, but they're still duplicating because we're displaying every run number instead of just the max.

       

      Any help would be greatly appreciated. Thank you!

        • Re: Grouping by max value of a field in relation to another field
          Sunny Talwar

          May be try this

           

          Table:

          LOAD [JobCode],

               [WorkCenter],

               [Run Number],

               [Time],

               [Qty]

          FROM ....;

           

          Right Join (Table)

          LOAD [JobCode],

               [WorkCenter],

               Max([Run Number]) as [Run Number]

          Resident Table

          Group By JobCode, WorkCenter;

          • Re: Grouping by max value of a field in relation to another field
            Felip Drechsler

            Here an example on how to do it.

             

            Hope it helps,

             

            Felipe.

            • Re: Grouping by max value of a field in relation to another field
              Antonio Mancini

              Hi Jacob,

              See Attachment

              Regards,

              Antonio

                • Re: Grouping by max value of a field in relation to another field
                  Ben VanderLaan

                  Hi,

                   

                  I can't open the .qvw (I'm thinking it's because I'm using personal edition and I've used all my recoveries up? Let me know if this is incorrect, or if there's a way around it)... but this is the type of solution I would want... so are your expressions just sum(Time) and sum(Qty), or something different?

                   

                  But besides that.. going further with this example.. if I want to display only rows where Run Number is the max Run Number for each jobcode, and my chart has the dimensions JobCode, WorkCenter, CostType, CompletedDate, Run Number, and expressions Sum(Time), Sum(Qty)... how would this be done?

                   

                  For example, this is what my chart displays:

                   

                  [JobCode]     [WorkCenter]      [CostType]     [CompletedDate]     [Run Number]     [Time]     [Qty]

                  1                  abc                        MR                    01/01/2017           1                       .27          20

                  1                  abc                        Run                   01/01/2017           1                       .30          25

                  1                  abc                        MR                    01/01/2017          2                       .27          20

                  1                  abc                        Run                   01/01/2017          2                       .30          25

                  1                  def                         MR                    01/01/2017          1                       .10          10

                  1                  def                         Run                   01/01/2017          1                       .15          15

                  1                  def                         MR                    01/01/2017          2                       .10          10

                  1                  def                         Run                   01/01/2017          2                       .15          15

                  2                  abc                        MR                    01/02/2017           3                       .33          33

                  2                  abc                        Run                   01/02/2017           3                       .32          50

                  2                  abc                        MR                    01/02/2017          4                       .33          33

                  2                  abc                        Run                   01/02/2017          4                       .32          50



                  And I want to know how to get the following:


                  [JobCode]     [WorkCenter]      [CostType]     [CompletedDate]     [Run Number]     [Time]     [Qty]

                  1                  abc                        MR                    01/01/2017          2                       .27          20

                  1                  abc                        Run                   01/01/2017          2                       .30          25

                  1                  def                         MR                    01/01/2017          2                       .10          10

                  1                  def                         Run                   01/01/2017          2                       .15          15

                  2                  abc                        MR                    01/02/2017           4                       .33          33

                  2                  abc                        Run                   01/02/2017           4                       .32          50



                  Does that make sense? - I've tried multiple ways (mixture of using AGGR(Max([Run Number]),[JobCode]) ; If([Run Number] = Max([Run Number]), [Run Number]); etc....)...each try was close, but not perfect... I usually ended up going back and forth trying to fix the dimensions and expressions... I didn't think it would be too difficult, but unfortunately I'm having a very hard time with this...  


                  Thanks in advance for any help!



                  -Ben

                • Re: Grouping by max value of a field in relation to another field
                  Sergey Shuklin

                  Hello, Jacob!

                   

                  Create a straight table with 3 dimensions:

                  1. JobCode

                  2. aggr(maxstring(dual(WorkCenter, RunNumber)), JobCode)

                  3. aggr(max(RunNumber), JobCode)

                   

                  As expressions use:

                  1. Only(Time)

                  2. Only(Qty)

                   

                  Should work.