topic Re: Summing records without including duplicates in QlikView

Summing records without including duplicates

Tue, 28 Jul 2015 14:51:29 GMT

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

Re: Summing records without including duplicates

robert_mika — Tue, 28 Jul 2015 15:39:42 GMT

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;

Re: Summing records without including duplicates

robert_mika — Tue, 28 Jul 2015 15:45:32 GMT

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]))

Re: Summing records without including duplicates

maxgro — Tue, 28 Jul 2015 19:25:55 GMT

// 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;