topic Re: Select records with max date in QlikView

Select records with max date

alisonpwallis — Wed, 27 Aug 2014 10:02:27 GMT

Hi,

I am trying to select a distinct group of records from within my data file. I want to get the record with the latest date for each ID. There could be 1, 2, 3, 4 or more records with the same ID but I only want the one with the latest date in each case. I have around 50,000 records total and I imagine I will be reducing this down by at least half when I deselect some records.

How can I load this from a qvd file so I only get the latest dated record for each ID?

Thanks for your help

Alison

Re: Select records with max date

MK_QSL — Wed, 27 Aug 2014 10:08:21 GMT

Try below

FirstSortedValue(Record, -Aggr(Max(Date),ID))

or provide 10-15 lines or sample data...

Re: Select records with max date

alisonpwallis — Wed, 27 Aug 2014 10:22:02 GMT

Thanks Manish

here is some sample data:

Key_SARAPPD Status Date of status change Sequence no

54546 U 1/1/13 1

54546 UF 28/8/13 2

54546 UF2 1/9/13 3

67856 C 2/3/13 1

67856 CF 1/4/13 2

67856 UF 28/8/13 3

67856 W 1/10/13 4

34577 C 1/2/13 1

34577 CD 3/4/13 2

29987 C 1/10/12 1

29987 CI 1/2/13 2

29987 UI 28/8/13 3

29987 W 29/8/13 4

Thanks

Alison

Re: Select records with max date

tresB — Wed, 27 Aug 2014 10:35:00 GMT

Straight table; dimension: Key_SARAPPD

Expr1: =FirstSortedValue(Status ,-[Date of status change ])

Expr2: =Max([Date of status change ])

Expr3: =FirstSortedValue(Sequence no,-[Date of status change ])

Re: Select records with max date

MK_QSL — Wed, 27 Aug 2014 10:52:30 GMT

I think Tresesco has already replied... If this is not working, let us know..

Re: Select records with max date

alisonpwallis — Wed, 27 Aug 2014 11:12:11 GMT

Is there no way of not even loading the other records at all? I want to do a number of charts and graphs with these records for various reports and I only want the latest record in each case. Can I do that somewhere in the load statement?

Thanks

Alison

Re: Select records with max date

tresB — Wed, 27 Aug 2014 11:54:48 GMT

Try:

Input:

Load * Inline [

Key_SARAPPD,Status,"Date of status change", Sequence no

54546,U,41275,1

54546,UF,41514,2

54546,UF2,41518,3

67856,C,41335,1

67856,CF,41365,2

67856,UF,41514,3

67856,W,41548,4

34577,C,41306,1

34577,CD,41367,2

29987,C,41183,1

29987,CI,41306,2

29987,UI,41514,3

29987,W,41515,4

];

Inner Join

Load Key_SARAPPD, Max("Date of status change") as "Date of status change" Resident Input group By Key_SARAPPD;

Note: You should not be worried about the numbers in the date field (they are actually date equivalent). PFA

Re: Select records with max date

alisonpwallis — Wed, 27 Aug 2014 12:14:28 GMT

This is exactly what I needed! Thank you so much