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

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

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

May be try this

Table:

[WorkCenter],

[Run Number],

[Time],

[Qty]

FROM ....;

Right Join (Table)

[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

Or this

Table:

[WorkCenter],

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

Only([Time]) as Time,

Only([Qty]) as Qty

FROM ....

Group By JobCode, WorkCenter;

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

It is preferred that no scripting would be done for this; do you have any suggestions on how I could do it without?

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

Remove duplicates without any scripting? Don't think that is possible.... but may be someone else have ideas

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

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

Hi Jacob,

See Attachment

Regards,

Antonio

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

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

Hi Ben

Regards,

Antonio

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

Hi Antonio,

Thanks for the reply - your solution works for the example, however using DISTINCT in the Sum will be an issue for me because that expression is summing all of the JobStepNum values (there are multiple JobStepNums for each JobCode, WorkCenter, etc.), and there are multiple JobStepNums within each job that may have the same qty... so I would still need to sum values that may be the same... Does that make sense?

Thanks,

Ben

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

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.