topic Re: How to pick latest moment on a day and lose rest of records? in QlikView

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

hansdevr — Fri, 15 Jul 2016 13:36:11 GMT

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!

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

sunny_talwar — Fri, 15 Jul 2016 13:40:35 GMT

I guess create a straight table like this:

Dimension:

Account#

Expressions:

1) TimeStamp(Max(Date))

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

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

marcus_sommer — Fri, 15 Jul 2016 14:40:07 GMT

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

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

maxgro — Fri, 15 Jul 2016 18:48:56 GMT

Another script solution could be

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

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

];

NoConcatenate

load *

Resident S

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

Order By Date desc;

DROP Table S;

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

hansdevr — Mon, 18 Jul 2016 07:22:32 GMT

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

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

hansdevr — Mon, 18 Jul 2016 07:43:56 GMT

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

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

marcus_sommer — Mon, 18 Jul 2016 07:54:41 GMT

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

- Marcus

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

hansdevr — Mon, 18 Jul 2016 08:25:01 GMT

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

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

hansdevr — Mon, 18 Jul 2016 08:28:57 GMT

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];

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

marcus_sommer — Mon, 18 Jul 2016 08:38:44 GMT

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

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

hansdevr — Mon, 18 Jul 2016 08:41:11 GMT

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

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

marcus_sommer — Mon, 18 Jul 2016 08:45:12 GMT

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

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

hansdevr — Mon, 18 Jul 2016 08:48:00 GMT

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

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

marcus_sommer — Mon, 18 Jul 2016 09:00:10 GMT

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

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

hansdevr — Mon, 18 Jul 2016 09:02:58 GMT

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()?!?

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

effinty2112 — Mon, 18 Jul 2016 09:06:01 GMT

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

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

hansdevr — Mon, 18 Jul 2016 09:08:46 GMT

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

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

hansdevr — Mon, 18 Jul 2016 09:09:52 GMT

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

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

effinty2112 — Mon, 18 Jul 2016 09:12:55 GMT

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

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

hansdevr — Mon, 18 Jul 2016 09:16:18 GMT

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