Grouping by max value of a field in relation to an... - Qlik Community

Report Inappropriate Content · ‎2017-06-19

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!

sunny_talwar · ‎2017-06-19

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;

sunny_talwar · ‎2017-06-19

Or this

Table:

LOAD [JobCode],

[WorkCenter],

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

Only([Time]) as Time,

Only([Qty]) as Qty

FROM ....

Group By JobCode, WorkCenter;

felipedl · ‎2017-06-19

Here an example on how to do it.

Hope it helps,

Felipe.

Report Inappropriate Content · ‎2017-06-19

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

sunny_talwar · ‎2017-06-19

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

antoniotiman · ‎2017-06-19

Hi Jacob,

See Attachment

Regards,

Antonio

benvatvandata · ‎2017-06-19

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

antoniotiman · ‎2017-06-19

Hi Ben

Regards,

Antonio

Sergey_Shuklin · ‎2017-06-20

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.

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