topic How to remove duplicate lines from AGGR in QlikView

How to remove duplicate lines from AGGR

tinkerz1 — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I am using the AGGR function to group on selected items but I am getting duplications on rows, how can I use AGGR to group on a date range, max() and a particular field item?

The code below is intended to bring back the MAX download Month Year Item per date range and where the issue is closed.

But I get 2 rows returned.

The data is uploaded once a month and will have duplicate IDs but the status will change according the status of the issue.

Thanks,

=aggr(max([Download Month-Year Item]) and [Issue - Status]='Closed' and date([Issue - Closed Date])>=addmonths(date(vMEEndate),-12),[Issue - ID],[Issue - Closed Date])

Issue - Location L2	Issue - ID	Issue - Closed Date	Issue - Owner	Issue - Status	Download Month-Year Item	Max Download Month Year Item
Europe	1	16-Dec-15	Mr A	Closed	01-Feb-16	-1
Europe	1	16-Dec-15	Mr A	Closed	01-Dec-15	-1
Europe	2	30-Sep-15	Mr B	Closed	01-Nov-15	-1
Europe	2	30-Sep-15	Mr B	Closed	01-Sep-15	-1

Re: How to remove duplicate lines from AGGR

Chanty4u — Mon, 18 Apr 2016 12:12:42 GMT

=aggr(Distinct max([Download Month-Year Item]) and [Issue - Status]='Closed' and date([Issue - Closed Date])>=addmonths(date(vMEEndate),-12),[Issue - ID],[Issue - Closed Date])

aggr(max([Download Month-Year Item]) and Distinct [Issue - Status]='Closed' and date([Issue - Closed Date])>=addmonths(date(vMEEndate),-12),[Issue - ID],[Issue - Closed Date])

Re: How to remove duplicate lines from AGGR

sunny_talwar — Mon, 18 Apr 2016 12:19:06 GMT

I am not completely sure I understand what you are trying to do. Do you have a sample you can share?

Re: How to remove duplicate lines from AGGR

hic — Mon, 18 Apr 2016 12:43:19 GMT

You use

aggr(

max([Download Month-Year Item]) and

[Issue - Status]='Closed' and

date([Issue - Closed Date])>=addmonths(date(vMEEndate),-12),

[Issue - ID],

[Issue - Closed Date]

)

The first parameter of the Aggr should be an aggregation, but you instead have a Boolean expression that only will return -1 or 0. Further, since you use naked field references, you may get undesired results.

Secondly, you say that you want one value per DateRange. But your Aggr() has both [Issue - ID] and [Issue - Closed Date] as dimensions. Then you will get one value per combination of these two, i.e. several per DateRange.

Thirdly, you have not wrapped the Aggr in an aggregation function. You should probably use a chart with DateRange as dimension and Max(Aggr(...)) as measure.

Re: How to remove duplicate lines from AGGR

swuehl — Mon, 18 Apr 2016 12:52:21 GMT

Try

Max(

{< [Issue - Status]={'Closed'}, [Issue - Closed Date] = {">=$(=Date(addmonths(vMEEndate,-12),'DD-MMM-YY'))"}>}[Download Month-Year Item])

as expression in your chart with dimension Download MonthYear item removed.