Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

hansdevr
Contributor II

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
Tags (1)
1 Solution

Accepted Solutions

Re: filtering the max amount per day in script

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;

9 Replies

Re: filtering the max amount per day in script

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
Contributor II

Re: filtering the max amount per day in script

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:

MVP
MVP

Re: filtering the max amount per day in script

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?

MVP
MVP

Re: filtering the max amount per day in script

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
Contributor II

Re: filtering the max amount per day in script

See above. Two records with Amounts for one date

Re: filtering the max amount per day in script

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
Contributor II

Re: filtering the max amount per day in script

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

hansdevr
Contributor II

Re: filtering the max amount per day in script

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

Re: filtering the max amount per day in script

Not a problem

We are glad that we were able to pinpoint the issue

Community Browser