How to load latest date from multiple dates

[#1]

Hi,

I have one question about loading dates from database.

For Example I have table as below (Customer prices).

ID  DATE            PRICE

1   3.2.2013           50,4

1  31.12.2012        60

1   5.9.2011           65

2   1.12.2012         2

2  24.9.2012          1,45

2   5.4.2012           3

3   15.3.2013         21,2

3  1.11.2012          19,3

3   3.9.2011           20,4

Now I would like only get infromation about current price which is active to customer based on current date (Result shoud be as example below). Just to reminder current date is 20.1.2013 when i wrote this post.

ID  DATE            PRICE

1  31.12.2012        60

2   1.12.2012         2

3  1.11.2012          19,3

What is the best way to do this?

Tried Max(Date) function,

FirstSortedValue with Group By condition

Thank you in advance!

[swuehl · Accepted solution]

FirstSortedValue() with a group by should do the trick (and limiting the records to past and current dates).

INPUT:

LOAD ID,

          Date(max(DATE)) as DATE,

          FirstSortedValue(PRICE,-DATE) as ValidPrice 

where DATE <= today() group by ID;

LOAD * INLINE [

ID DATE PRICE

1 3.2.2013 '50,4'

1 31.12.2012 60

1 5.9.2011 65

2 1.12.2012 2

2 24.9.2012 '1,45'

2 5.4.2012 3

3 15.3.2013 21,2

3 1.11.2012 '19,3'

3 3.9.2011 '20,4'

] (delimiter is ' ');

[#3]

HI

     see attachement..

[#4]

Thank you! Works like charm!

[#5]

Thank you for the example! Was very helpful!