Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Summing records without including duplicates

I have some records that I am trying to sum, but have duplicate line items because of the report I am utilizing. I want to be able to select the last or max value of the duplicate records and use it to create the sum. Can someone help me out? Here is an example of the records:

Notification, work center, cost

2011, 1010, 456

2011, 1010, 34

2011, 1010, 79

2012, 1023, 765

2012, 1023, 903

2013, 1023, 23

2013, 1020, 45

So if I were to get the records to the right state for summing, they would look like the items below without the duplicates:

2011, 1010, 456

2012, 1023, 903

2013, 1023, 23

2013, 1020, 45

Thanks for the help

1 Solution

Accepted Solutions
robert_mika
Master III
Master III

Data:

LOAD *

Inline [

Notification, work center, cost

2011, 1010, 456

2011, 1010, 34

2011, 1010, 79

2012, 1023, 765

2012, 1023, 903

2013, 1023, 23

2013, 1020, 45

]

;

final:

load

Notification as Not,

[work center] as Work,

max(cost) as Mcost

Resident Data

Group by

Notification,

[work center];

drop table Data;

2015-07-28_163913.png

View solution in original post

3 Replies
robert_mika
Master III
Master III

Data:

LOAD *

Inline [

Notification, work center, cost

2011, 1010, 456

2011, 1010, 34

2011, 1010, 79

2012, 1023, 765

2012, 1023, 903

2013, 1023, 23

2013, 1020, 45

]

;

final:

load

Notification as Not,

[work center] as Work,

max(cost) as Mcost

Resident Data

Group by

Notification,

[work center];

drop table Data;

2015-07-28_163913.png

robert_mika
Master III
Master III

or in the front end:

Data:

LOAD *

Inline [

Notification, work center, cost

2011, 1010, 456

2011, 1010, 34

2011, 1010, 79

2012, 1023, 765

2012, 1023, 903

2013, 1023, 23

2013, 1020, 45

]

Expression

=max(aggr(max(cost),Notification,[work center]))

2015-07-28_164543.png

maxgro
MVP
MVP

1.png

// your data

//

source:

load * inline [

Notification, work center, cost

2011, 1010, 456

2011, 1010, 34

2011, 1010, 79

2012, 1023, 765

2012, 1023, 903

2013, 1023, 23

2013, 1020, 45

];

final:

NoConcatenate load *

Resident source

where

  Notification<>peek(Notification) or Notification=Peek(Notification) and [work center]<>Peek([work center])

order by [work center], Notification, cost desc;

DROP Table source;