Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
hi
create a table
reportId as dimension
count(distinct ReportDate) as expression
I don't think this would work... because ReportId = 1 has only 3 distinct dates
1 | 20/01/2018 |
1 | 22/01/2018 |
1 | 02/03/2018 |
1 | 02/03/2018 |
1 | 02/03/2018 |
1 | 02/03/2018 |
Similarly, ReportId = 4 has only 2 distinct dates....
Not sure I understand the logic... can you please elaborate pooja_sn
Hi,
Looking for below solution
in back end modify your script like
Test:
Load ReportId,date(Date#(ReportDate,'DD/MM/YYYY')) as ReportDate 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
Load ReportId,Date(max(ReportDate)) as maxDate Resident Test Group by ReportId;
Final:
Load ReportId,maxDate,ReportDate,if(ReportDate=maxDate,1,0) as flag Resident Test;
drop table Test;
In front end take straight table
dimension : ReportId
Expression: sum(flag)
Regards
May be try
T1:
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(T1)
Load
ReportId,
Max(ReportDate) as ReportDate,
1 as MaxReportDate
Resident T1
Group By
ReportId
;
in the front end, user reportid as dimension
with expression
count({<MaxReportDate={1}>} ReportId)