how to load max date row from the table ?

[gauravgg]

hi all

I have the following table

ID	Date	Amount
001	01-Jan-2016	1000
001	02-Feb-2016	2000
001	01-Mar-2016	4000
002	01-Aug-2016	6000
002	01-Dec-2016	7000

From the above table i have to load only those records which has max date for each ID

the output should be as follows

ID	Date	Amount
001	01-Mar-2016	4000
002	01-Dec-2016	7000

can anyone help me in this ?

Thanks in advance

[sunny_talwar]

Try this script:

Table:

LOAD

    ID,

    "Date",

    Amount

FROM [lib://Web]

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

Right Join (Table)

LOAD ID,

  Max(Date) as Date

Resident Table

Group by ID;

[sunny_talwar]

You can also try this:

Table:

LOAD

    ID,

    "Date",

    Amount

FROM [lib://Web]

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

FinalTable:

NoConcatenate

LOAD *

Resident Table

Where ID <> Previous(ID)

Order By ID, Date desc;

[gauravgg]

thanks sunny

[timrogers13]

Thanks Sunny.  This helped me out. -Tim

[kfloresc]

thank you so much! you have no idea how much this helped, i've been trying to figure out something similar for days now!