QlikView App Dev

[#1]

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

[robert_mika · Accepted solution]

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;

[robert_mika]

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

[maxgro]

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