Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
hansdevr
Contributor II

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

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!

Tags (1)
1 Solution

Accepted Solutions

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

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

20 Replies

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

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?

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

MVP
MVP

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

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;

1.png

hansdevr
Contributor II

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

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

hansdevr
Contributor II

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

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?

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

- Marcus

hansdevr
Contributor II

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

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

hansdevr
Contributor II

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

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?

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

Community Browser