Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
`Hi Everyone,
I have a (simplified) table, in which there are more than one entries for Amount on one day.
What I want, is just one records per Dossier#, per Date, which shows the highest value for Amount on that day. All other records should be dropped.
Now I know, this should be done with max() and group by, but I can't get it to work properly.
Any ideas?
Original table:
Dossier# | Date | Amount |
1001 | 11-2-2016 | € 100,00 |
1001 | 11-2-2016 | € 150,00 |
1001 | 14-2-2016 | € 200,00 |
1001 | 14-2-2016 | € 350,00 |
1001 | 15-2-2016 | € 500,00 |
1002 | 1-1-2016 | € 120,00 |
1002 | 2-1-2016 | € 170,00 |
1002 | 2-1-2016 | € 190,00 |
1002 | 6-1-2016 | € 250,00 |
1003 | 1-4-2016 | € 500,00 |
1003 | 6-4-2016 | € 700,00 |
Desired result:
Dossier# | Date | Amount |
1001 | 11-2-2016 | € 150,00 |
1001 | 14-2-2016 | € 350,00 |
1001 | 15-2-2016 | € 500,00 |
1002 | 1-1-2016 | € 120,00 |
1002 | 2-1-2016 | € 190,00 |
1002 | 6-1-2016 | € 250,00 |
1003 | 1-4-2016 | € 500,00 |
1003 | 6-4-2016 | € 700,00 |
Can you try this? May be Date looks like a date, but is actually a timestamp?
Temp_1:
LOAD //%DossierNr,
%ClaimVlgnr,
[%Reserve referentie],
Ranking,
[Reserve regelnr.],
Date(Floor([Res. aanmaakdatum]), 'YYYY-MM-DD') as [Datum],
[Reserve orig.valuta],
[Reserve valutacode],
[Reserve wisselkoers],
[Reserve bedrag in €],
Schadecategorie,
[Reserve status]
FROM
$(vQVDfolder)QVD Reserveregels_WBF.qvd
(qvd)
where [Reserve status] = 'Boeking geslaagd' and [Reserve bedrag in €]<>0
;
Temp_2:
LOAD
%ClaimVlgnr,
Datum,
max([Reserve bedrag in €]) as Stand
Resident Temp_1
group by %ClaimVlgnr, Datum ;
Drop table Temp_1;
May be try this:
Table:
LOAD Dossier#,
Date,
Max(Amount) as Amount
FROM
[https://community.qlik.com/thread/220749]
(html, codepage is 1252, embedded labels, table is @1)
Group By Dossier#, Date;
I thought so.. But I already tried this:
Here's my script, with other variable names..
Temp_1:
LOAD //%DossierNr,
%ClaimVlgnr,
[%Reserve referentie],
Ranking,
[Reserve regelnr.],
date([Res. aanmaakdatum], 'YYYY-MM-DD') as [Datum],
[Reserve orig.valuta],
[Reserve valutacode],
[Reserve wisselkoers],
[Reserve bedrag in €],
Schadecategorie,
[Reserve status]
FROM
$(vQVDfolder)QVD Reserveregels_WBF.qvd
(qvd)
where [Reserve status] = 'Boeking geslaagd' and [Reserve bedrag in €]<>0
;
Temp_2:
LOAD
%ClaimVlgnr,
Datum,
max([Reserve bedrag in €]) as Stand
Resident Temp_1
group by %ClaimVlgnr, Datum ;
Drop table Temp_1;
And this is the result:
Now I know, this should be done with max() and group by, but I can't get it to work properly.
Any ideas?
It should be quite straight forward,like Sunny suggested.
If this doesn't work, what issues do you see?
Are you sure your dates are not timestamps:
LOAD
%ClaimVlgnr,
DayName(Datum) as Datum,
max([Reserve bedrag in €]) as Stand
Resident Temp_1
group by %ClaimVlgnr, Floor(Datum) ;
Drop table Temp_1;
See above. Two records with Amounts for one date
Can you try this? May be Date looks like a date, but is actually a timestamp?
Temp_1:
LOAD //%DossierNr,
%ClaimVlgnr,
[%Reserve referentie],
Ranking,
[Reserve regelnr.],
Date(Floor([Res. aanmaakdatum]), 'YYYY-MM-DD') as [Datum],
[Reserve orig.valuta],
[Reserve valutacode],
[Reserve wisselkoers],
[Reserve bedrag in €],
Schadecategorie,
[Reserve status]
FROM
$(vQVDfolder)QVD Reserveregels_WBF.qvd
(qvd)
where [Reserve status] = 'Boeking geslaagd' and [Reserve bedrag in €]<>0
;
Temp_2:
LOAD
%ClaimVlgnr,
Datum,
max([Reserve bedrag in €]) as Stand
Resident Temp_1
group by %ClaimVlgnr, Datum ;
Drop table Temp_1;
You might have a very good point there!! Let me try this!
And YES!! So easy to overlook this... Thank you very much, Sunny!!!
Not a problem
We are glad that we were able to pinpoint the issue