count of maxDates only

[pooja_sn]

Hi,

I have this raw data in qlikview having multiple ReportIds & each Id having ReportDates. I need to show a pivot displaying count of ReportId's  having maximum ReportDate only for that particular report.

Input data:

ReportId	ReportDate
1	20/01/2018
1	22/01/2018
1	02/03/2018
4	28/08/2018
4	05/12/2018
5	15/07/2018
6	21/09/2018
1	02/03/2018
1	02/03/2018
1	02/03/2018
4	05/12/2018
4	05/12/2018
4	05/12/2018

Output:

ReportId	Volume
1	4
4	3
5	1
6	1

[lironbaram]

hi

build a table with

reportid as dimension    

count(distinct ReportDate) as expression

[tresesco]

Try :

Dim: ReportId

Exp: Count(if(ReportDate=Aggr(NODISTINCT Max(ReportDate), ReportId), ReportDate))

[YoussefBelloum]

Hi,

A third "script" solution would be this:

reportid:

LOAD ReportId, ReportDate, RowNo() as key;

LOAD * Inline [

ReportId, ReportDate

1, 20/01/2018

1, 22/01/2018

1, 02/03/2018

4, 28/08/2018

4, 05/12/2018

5, 15/07/2018

6, 21/09/2018

1, 02/03/2018

1, 02/03/2018

1, 02/03/2018

4, 05/12/2018

4, 05/12/2018

4, 05/12/2018

];

Left Join(reportid)

LOAD

ReportId, Date(Max(ReportDate)) as MaxReportDate

Resident reportid

Group by ReportId;

final:

LOAD *, if(ReportDate=MaxReportDate, 1,0) as flag_max

Resident reportid;

DROP Table reportid;

PFA

[vishalarote]

Table:

load *,recno() as Flag,

Date(Date#(ReportDate,'DD/MM/YYYY'),'DD/MM/YYYY') as date Inline [

ReportId, ReportDate

1, 20/01/2018

1, 22/01/2018

1, 02/03/2018

4, 28/08/2018

4, 05/12/2018

5, 15/07/2018

6, 21/09/2018

1, 02/03/2018

1, 02/03/2018

1, 02/03/2018

4, 05/12/2018

4, 05/12/2018

4, 05/12/2018

];

drop Field ReportDate;

left join(Table)

t1:

load ReportId,

date(Max(date),'DD/MM/YYYY') as V

Resident Table

Group by ReportId;

left join(Table)

load *,

if(date=V,1,0) as v2

Resident Table;

t2:

load ReportId,

Count(v2) as Volume

Resident Table

where v2=1

Group by ReportId;

[pooja_sn]

Cool. and How do i modify this expression to add some more conditions if i want this pivot for Region=MA only

[tresesco]

Try like:

Count( {<Region={'MA'}>} if(ReportDate=Aggr(NODISTINCT Max({<Region={'MA'}>} ReportDate), ReportId), ReportDate))