Load maximum date only

[#1]

hi there!

I have question to ask

sample table like this (format date : mm/dd/yyyy):

ID          Name          Date

1           John            1/6/2011

1           John            3/2/2011

1           John            4/20/2011

2           Andy            2/1/2011

3           Jean            3/3/2011

3           Jean            4/7/2011

4           Chris            1/1/2011

what i want is to load the table where the date is maximum date like this :

ID          Name          Date

1           John            4/20/2011

2           Andy            2/1/2011

3           Jean            4/7/2011

4           Chris            1/1/2011

how to solve it? thanks all

[deepakk]

hi,

Use max function with group by clause to get the reuslt.

Load ID, Name, Date(Max(Date),'mm/dd/yyyy') as Date

from table group by ID, Name;

Deepak

[#3 · Accepted solution]

Hi,

see also this example for help (Solution by script and by expression in a table)

Good luck!

Rainer

[jagan]

Hi,

You can load only Max dates by using the script below

Load

     ID,

     Name,

     Date(Max(Date),'mm/dd/yyyy') as Date

FROM FileName

GROUP BY ID, Name;

Or

You can use

Max(Date) in the expression, by using ID and Name as Dimensions.

Regards,

jagan.