Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
hansdevr
Creator III
Creator III

filtering the max amount per day in script

`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#DateAmount
100111-2-2016€ 100,00
100111-2-2016€ 150,00
100114-2-2016€ 200,00
100114-2-2016€ 350,00
100115-2-2016€ 500,00
10021-1-2016€ 120,00
10022-1-2016€ 170,00
10022-1-2016€ 190,00
10026-1-2016€ 250,00
10031-4-2016€ 500,00
10036-4-2016€ 700,00

Desired result:

   

Dossier#DateAmount
100111-2-2016€ 150,00
100114-2-2016€ 350,00
100115-2-2016€ 500,00
10021-1-2016€ 120,00
10022-1-2016€ 190,00
10026-1-2016€ 250,00
10031-4-2016€ 500,00
10036-4-2016€ 700,00
1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

9 Replies
sunny_talwar

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;

hansdevr
Creator III
Creator III
Author

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:

swuehl
MVP
MVP

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?

swuehl
MVP
MVP

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;

hansdevr
Creator III
Creator III
Author

See above. Two records with Amounts for one date

sunny_talwar

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;

hansdevr
Creator III
Creator III
Author

You might have a very good point there!! Let me try this!

hansdevr
Creator III
Creator III
Author

And YES!! So easy to overlook this... Thank you very much, Sunny!!!

sunny_talwar

Not a problem

We are glad that we were able to pinpoint the issue