Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

hansdevr
Contributor III

Tried max() and Group by in combination with right join...

Hi Everyone,

In the table below, you see cumlative amounts, resulting from financial mutations. This is the original table.

Because I want to use intervalmatch(), I would like to translate this table into a much more simpe table.

When there are more mutations on one day, I would like to keep the mutation for the highest point of time for that day - the rest should go. At the same time, I want to translate the complex day to a start- and ending date for the period in which that mutation is valid.

Sometimes, start and end date will be equal. The end date for the most recent mutation should be today().

Original:

     

Dossier#Claim#RankingCreation_dateCumulative_amount
1000110001-1701-1-2015 17:15:32€ 0,00
1000110001-1605-1-2015 13:10:11€ 15.000,00
1000110001-1505-1-2015 14:20:17€ 14.000,00
1000110001-1417-1-2015 07:10:09€ 13.000,00
1000110001-1317-1-2015 07:10:35€ 17.000,00
1000110001-1217-1-2015 09:16:51€ 14.000,00
1000110001-1118-1-2015 20:08:00€ 11.000,00
1000210002-1114-3-2016 16:15:00€ 18.000,00

Desired result, after translation (both Ranking and Creation date can be dropped):

    

Dossier#Claim#Start dateEnd dateCumulative_amount
1000110001-101-1-201504-1-2015€ 0,00
1000110001-105-1-201516-1-2015€ 14.000,00
1000110001-117-1-201517-1-2015€ 14.000,00
1000110001-118-1-201523-6-2016€ 11.000,00
1000210002-114-3-201623-6-2016€ 18.000,00

I hope I made it clear... Thoughts anyone? And yes, I NEED to do this in the loading script...

1 Solution

Accepted Solutions
MVP
MVP

Re: Tried max() and Group by in combination with right join...

Ok, I think we need to order by ClaimVlgnr and Date, and in addition you need to use a minus sign in the FirstSortedValue() sort weight:

Result:
LOAD *,
if(Previous(ClaimVlgnr) <> ClaimVlgnr, Today(), date(previous([Datum start])-1)) as [Datum Einde];
Load
%DossierNr,
ClaimVlgnr,
date(floor(Max([Res. aanmaakdatum]))) as [Datum start],
FirstSortedValue([Reserve bedrag in €], -[Res. aanmaakdatum]) as [Reserve bedrag in €]
Resident Temp_1 group by %DossierNr, ClaimVlgnr, DayName([Res. aanmaakdatum])
order by ClaimVlgnr, [Res. aanmaakdatum] desc;

DROP TABLE Temp_1;

8 Replies
MVP
MVP

Re: Tried max() and Group by in combination with right join...

Try something like

Set TimeStampFormat ='DD-M-YYYY hh:mm:ss';

Dossiers:

LOAD *INLINE [

Dossier# Claim# Ranking Creation_date Cumulative_amount

10001 10001-1 7 01-1-2015 17:15:32 € 0,00

10001 10001-1 6 05-1-2015 13:10:11 € 15.000,00

10001 10001-1 5 05-1-2015 14:20:17 € 14.000,00

10001 10001-1 4 17-1-2015 07:10:09 € 13.000,00

10001 10001-1 3 17-1-2015 07:10:35 € 17.000,00

10001 10001-1 2 17-1-2015 09:16:51 € 14.000,00

10001 10001-1 1 18-1-2015 20:08:00 € 11.000,00

10002 10002-1 1 14-3-2016 16:15:00 € 18.000,00

] (delimiter is '\t');

LOAD *, If(previous(Claim#)<> Claim#, Today(), Date(previous(StartDate)-1)) as EndDate;

LOAD Dossier#, Claim#, Date(Floor(Max(Creation_date)))as StartDate, FirstSortedValue(Cumulative_amount,-Creation_date) as Cumulative_amount

RESIDENT Dossiers

GROUP BY Dossier#, Claim#, DayName(Creation_date)

ORDER BY Creation_date desc;

DROP TABLE Dossiers;

Dossier# Claim# Cumulative_amount StartDate EndDate
1000110001-1€ 0,0001-1-201504-1-2015
1000110001-1€ 11.000,0018-1-201520-6-2016
1000110001-1€ 14.000,0005-1-201516-1-2015
1000110001-1€ 14.000,0017-1-201517-1-2015
1000210002-1€ 18.000,0014-3-201620-6-2016
hansdevr
Contributor III

Re: Tried max() and Group by in combination with right join...

Thanks for your swift reply, Stefan!

Will try this and get back to you!

hansdevr
Contributor III

Re: Tried max() and Group by in combination with right join...

Hmm, tried it..

I get equal end dates (all today()) and I believe in your result, there is a little mistake.. I am missing the today() end date in Claim # 10001-1...

MVP
MVP

Re: Tried max() and Group by in combination with right join...

Have you tried to run my sample script? Or did you modify the script to adapt to your source?

If you get end date today() for all records, I think we need to adapt the condition that checks on a change in Claim#.

Edit: And there is a today() end date in my sample table attached to my previous post, it's just the I added the script to a QVW already opened for some time and thus today() returned Jun 20.

hansdevr
Contributor III

Re: Tried max() and Group by in combination with right join...

i adapted your script for mine. Here's my source script:

Temp_1:

LOAD

    %DossierNr,

    %ClaimVlgnr                    as ClaimVlgnr,

    Ranking,

    [Reserve regelnr.],

    [Res. aanmaakdatum],

    [Reserve bedrag in €],

    [Reserve status]

FROM

$(vQVDfolder)QVD Reserveregels_WBF.qvd

(qvd)

where [Reserve status] = 'Boeking geslaagd'

;

// sommeer losse reserves in regelnummers..

right join(Temp_1)

LOAD

    %DossierNr,

    ClaimVlgnr,

    Ranking,

    min([Reserve regelnr.])        as [Reserve regelnr.],

    only([Res. aanmaakdatum])    as [Res. aanmaakdatum],

    only([Reserve status])         as [Reserve status],

    sum([Reserve bedrag in €])    as [Reserve bedrag in €]

Resident Temp_1 group by %DossierNr, ClaimVlgnr, Ranking;   

// kies stand reserve voor 1 dag, nl. op het meest late tijdstip van die dag..

Result:

LOAD *,

    if(Previous(ClaimVlgnr) <> ClaimVlgnr, Today(), date(previous([Datum start]-1))) as [Datum Einde];

    Load

    %DossierNr,

    ClaimVlgnr,

    date(floor(Max([Res. aanmaakdatum]))) as [Datum start],

    FirstSortedValue([Reserve bedrag in €], -[Res. aanmaakdatum]) as [Reserve bedrag in €]

    Resident Temp_1 group by %DossierNr, ClaimVlgnr, DayName([Res. aanmaakdatum])

    order by [Res. aanmaakdatum] desc;

 

DROP TABLE Temp_1; 

exit script;

hansdevr
Contributor III

Re: Tried max() and Group by in combination with right join...

OK, I see a little mistake I made, but that didn't do the trick...

Result:
LOAD *,
if(Previous(ClaimVlgnr) <> ClaimVlgnr, Today(), date(previous([Datum start])-1)) as [Datum Einde];
Load
%DossierNr,
ClaimVlgnr,
date(floor(Max([Res. aanmaakdatum]))) as [Datum start],
FirstSortedValue([Reserve bedrag in €], [Res. aanmaakdatum]) as [Reserve bedrag in €]
Resident Temp_1 group by %DossierNr, ClaimVlgnr, DayName([Res. aanmaakdatum])
order by [Res. aanmaakdatum] desc;

DROP TABLE Temp_1; 

MVP
MVP

Re: Tried max() and Group by in combination with right join...

Ok, I think we need to order by ClaimVlgnr and Date, and in addition you need to use a minus sign in the FirstSortedValue() sort weight:

Result:
LOAD *,
if(Previous(ClaimVlgnr) <> ClaimVlgnr, Today(), date(previous([Datum start])-1)) as [Datum Einde];
Load
%DossierNr,
ClaimVlgnr,
date(floor(Max([Res. aanmaakdatum]))) as [Datum start],
FirstSortedValue([Reserve bedrag in €], -[Res. aanmaakdatum]) as [Reserve bedrag in €]
Resident Temp_1 group by %DossierNr, ClaimVlgnr, DayName([Res. aanmaakdatum])
order by ClaimVlgnr, [Res. aanmaakdatum] desc;

DROP TABLE Temp_1;

hansdevr
Contributor III

Re: Tried max() and Group by in combination with right join...

That sure did the trick, Stefan!! Would you like to explain your script?