topic Re: filtering the max amount per day in script in QlikView

filtering the max amount per day in script

hansdevr — Tue, 14 Jun 2016 11:32:01 GMT

`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

Re: filtering the max amount per day in script

sunny_talwar — Tue, 14 Jun 2016 11:36:31 GMT

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;

Re: filtering the max amount per day in script

hansdevr — Tue, 14 Jun 2016 11:41:41 GMT

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:

Re: filtering the max amount per day in script

swuehl — Tue, 14 Jun 2016 11:42:50 GMT

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?

Re: filtering the max amount per day in script

swuehl — Tue, 14 Jun 2016 11:43:50 GMT

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;

Re: filtering the max amount per day in script

hansdevr — Tue, 14 Jun 2016 11:43:51 GMT

See above. Two records with Amounts for one date

Re: filtering the max amount per day in script

sunny_talwar — Tue, 14 Jun 2016 11:43:58 GMT

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;

Re: filtering the max amount per day in script

hansdevr — Tue, 14 Jun 2016 11:44:56 GMT

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

Re: filtering the max amount per day in script

hansdevr — Tue, 14 Jun 2016 11:46:36 GMT

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

Re: filtering the max amount per day in script

sunny_talwar — Tue, 14 Jun 2016 11:48:33 GMT

Not a problem

We are glad that we were able to pinpoint the issue