Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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

Tags (2)
1 Solution

Accepted Solutions

Re: Summing records without including duplicates

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

3 Replies

Re: Summing records without including duplicates

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

Re: Summing records without including duplicates

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

MVP
MVP

Re: Summing records without including duplicates

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;

Community Browser