How to pick latest moment on a day and lose rest of records?

[hansdevr]

I have a table with multiple accountnumbers. Here is only one, of many more. As you can see, there can be several mutations in the Cumulative amount per day. What I want is a table that lists just one day at a time, with the cumulative amount that was present at the latest time of that day. The rest can be discarded.

Any help would be much appreciated!

[hansdevr]

To be honest, I was under the impression that the only way of including a field in an aggregation, which is not part of the aggregation, but for purposes of preserving it, is using the only() function... If that is not the case, I guess you should blame my lack of QV experience...

[marcus_sommer]

Try:

T2:
NoConcatenate
Load %DossierNr,
%ClaimVlgnr,
Ranking,
[Res. aanmaakdatum],
Schadecategorie as Categorie,
sum([Reserve bedrag in €]) as [Reserve bedrag in €]
Resident T1 Group by %DossierNr, %ClaimVlgnr, Ranking, [Res. aanmaakdatum], Schadecategorie;
Drop table T1;

// Stap 2: determine per day lowest ranking (latest day moment)

T3:
load %DossierNr, %ClaimVlgnr, date(floor([Res. aanmaakdatum])) as [Res. aanmaakdatum], [Res. aanmaakdatum] as Timestamp resident T2;
inner join (T3)

load %DossierNr, %ClaimVlgnr, timestamp(max(Timestamp)) as Timestamp resident T3 group by %DossierNr, %ClaimVlgnr, [Res. aanmaakdatum];

- Marcus

[hansdevr]

Wouldn't I lose [Reserve bedrag in €] that way? (My original cumulative amt)

[marcus_sommer · Accepted solution]

Then add these field to T3:

T3:
load

     %DossierNr, %ClaimVlgnr, date(floor([Res. aanmaakdatum])) as [Res. aanmaakdatum],

     [Res. aanmaakdatum] as Timestamp, [Reserve bedrag in €]
resident T2;
inner join (T3)

load %DossierNr, %ClaimVlgnr, timestamp(max(Timestamp)) as Timestamp resident T3 group by %DossierNr, %ClaimVlgnr, [Res. aanmaakdatum];

- Marcus

[hansdevr]

Marcus, it worked like a charm. Thank you ever so much. Would you please be so kind as to explain what your script exactly does and why I should not use only()?!?

[effinty2112]

Hans,

          Is this any good for you?

Account#	=Date(Floor(Date))	Timestamp(Max(Date))	FirstSortedValue(Line#,-Date)	FirstSortedValue(CumAmount,-Date)
1001001	4-7-2016	4-7-2016 07:15:30	1	1400
1001001	2-7-2016	2-7-2016 17:10:11	2	-
1001001	1-7-2016	1-7-2016 15:10:25	5	1350

Cheers

Andrew

[hansdevr]

Yes, that might well work, but I was looking for a scripting solution. Nevertheless, thank you very much!

[hansdevr]

...most probably because of the problems Marcus mentioned in his thread...

[effinty2112]

Sorry Hans, I really should read questions with more care!

[hansdevr]

Nonsense, don't be sorry - any response was more than welcome!!