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!

[sunny_talwar]

I guess create a  straight table like this:

Dimension:

Account#

Expressions:

1) TimeStamp(Max(Date))

2) FirstSortedValue([Cumulative amt.], -Date)

[marcus_sommer]

A script-solution could be:

table:

load Account#, date(floor(Date)) as Date, Date as Timestamp, [Cumulative amt.] From YourSource;

     inner join

load Account#, Date, timestamp(max(Timestamp)) as Timestamp resident table group by Account#, Date;

Instead of an inner join you could also use a left join or maybe a mapping to flag your max. Timestamp.

- Marcus

[maxgro]

Another script solution could be

SET TimestampFormat='D-M-YYYY hh:mm:ss[.fff]';

S:

load * inline [

Account#, Date, Line#, CumAmount

1001001, 4-7-2016 07:15:30, 1, 1400

1001001, 2-7-2016 17:10:11, 2, -

1001001, 2-7-2016 13:15:45, 3, 1550

1001001, 2-7-2016 12:10:19, 4, 750

1001001, 1-7-2016 15:10:25, 5, 1350

];

F:

NoConcatenate

load *

Resident S

where Floor(Date) <> Floor(Peek('Date'))

Order By Date desc;

DROP Table S;

[hansdevr]

I am sorry, maxgro, but unfortunately, your solution did not work for me..

[hansdevr]

Hi Marcus,

Tried your solution, but for some reason it didn't work for me either.. I kept getting errors on the field Timestamp not existing..

[marcus_sommer]

Please provide the script which you are using or a small sample-application with a few inline-data.

- Marcus

[hansdevr]

Unfortunately, in that table I only got ONE value, where I expected around 35...

[hansdevr]

T1:
LOAD
%DossierNr,
%ClaimVlgnr,
[%Reserve referentie],
Ranking,
[Reserve regelnr.],
[Res. aanmaakdatum],
[Reserve orig.valuta],
[Reserve valutacode],
[Reserve wisselkoers],
[Reserve bedrag in €],
Schadecategorie,
[Reserve status]
FROM

(qvd)
where [Reserve status] = 'Boeking geslaagd'
;

// Stap 1: aggregate reserves on ranking level
T2:
NoConcatenate
Load %DossierNr,
%ClaimVlgnr,
Ranking,
only([Res. aanmaakdatum]) as [Res. aanmaakdatum],
only(Schadecategorie) as Categorie,
sum([Reserve bedrag in €]) as [Reserve bedrag in €]
Resident T1 Group by %DossierNr, %ClaimVlgnr, Ranking;
Drop table T1;

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

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

[marcus_sommer]

Your error-message (field didn't exists) comes from referring the false table T2 instead of T3. Further I see that you use the function only() within the first-part of loading the T3 without specifying a group by statement - are you sure that you need the only-aggregation there (quite similar within the load of T2)?

- Marcus