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

[hansdevr]

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#	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

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

Dossier#	Claim#	Start date	End date	Cumulative_amount
10001	10001-1	01-1-2015	04-1-2015	€ 0,00
10001	10001-1	05-1-2015	16-1-2015	€ 14.000,00
10001	10001-1	17-1-2015	17-1-2015	€ 14.000,00
10001	10001-1	18-1-2015	23-6-2016	€ 11.000,00
10002	10002-1	14-3-2016	23-6-2016	€ 18.000,00

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

[swuehl]

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
10001	10001-1	€ 0,00	01-1-2015	04-1-2015
10001	10001-1	€ 11.000,00	18-1-2015	20-6-2016
10001	10001-1	€ 14.000,00	05-1-2015	16-1-2015
10001	10001-1	€ 14.000,00	17-1-2015	17-1-2015
10002	10002-1	€ 18.000,00	14-3-2016	20-6-2016

[hansdevr]

Thanks for your swift reply, Stefan!

Will try this and get back to you!

[hansdevr]

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...

[swuehl]

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]

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]

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; 

[swuehl · Accepted solution]

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]

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