topic Re: Only Display Rows With Highest Date in QlikView

Only Display Rows With Highest Date

haymarketpaul — Fri, 10 Apr 2015 15:49:37 GMT

I have data like the following where users have registered more once in the same system but with different details and i want the latest row for each user based on the FinalRecencyDate.

It is the GMC number that determines the users are the same regardless of name or email so i just want to display the latest one

UserID	Firstname	Lastname	Email	GMC	FinalRecencyDate
1	Joe	Bloggs	jb@myemail.com	1234567	15/02/2015
2	-	Bloggs	joebloggs@email.com	1234567	02/04/2015
3	Jane	Doe	jane@email.co.uk	7654321	02/01/2015
4	J	Doe	jane.doe@email.com	7654321	23/03/215

So looking at the above data i would only want to display UserID 2 and UserID 4

Any ideas how best to filter out the unneeded rows? Ultimately i just need it in a straight table or table box to output and pass on to someone.

I've tried using Group By but there are actually about 40 fields in total so not sure that's wise

Re: Only Display Rows With Highest Date

Anonymous — Fri, 10 Apr 2015 17:00:54 GMT

See attached

Re: Only Display Rows With Highest Date

Anonymous — Fri, 10 Apr 2015 18:14:21 GMT

You could try like below,

Data:

LOAD * INLINE [

userid, fname, lname, email, gmc, frdate

1, Joe, Bloggs, jb@myemail.com, 1234567,02/15/2015

2, -, Bloggs, joebloggs@email.com, 1234567,04/02/2015

3, Jane, Doe, jane@email.co.uk, 7654321,01/02/2015

4, J, Doe, jane.doe@email.com, 7654321,03/23/2015

];

Data2:

NoConcatenate

Load

userid,

fname,

lname,

email,

gmc as gmc2,

frdate

resident Data

where not Exists(gmc2,gmc)

order by

frdate desc

;

drop table Data;

Re: Only Display Rows With Highest Date

maxgro — Fri, 10 Apr 2015 18:42:36 GMT

source:

LOAD UserID,

Firstname,

Lastname,

Email,

GMC,

FinalRecencyDate

FROM

[https://community.qlik.com/thread/159701]

(html, codepage is 1252, embedded labels, table is @1);

final:

NoConcatenate load *

Resident source

where Peek(GMC)<>GMC // only the first GMC

order by GMC, FinalRecencyDate desc; // order by .........

DROP Table source;

Re: Only Display Rows With Highest Date

haymarketpaul — Mon, 13 Apr 2015 14:07:01 GMT

Thanks All - 3 interestingly different answers