Select records with max date

[alisonpwallis]

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

[MK_QSL]

Try below

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

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

[alisonpwallis]

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                         

[tresesco]

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

[MK_QSL]

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

[alisonpwallis]

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

[tresesco · Accepted solution]

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

[alisonpwallis]

This is exactly what I needed! Thank you so much