topic how to load max date row from the table ? in App Development

how to load max date row from the table ?

gauravgg — Sat, 08 Oct 2016 06:33:30 GMT

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

Re: how to load max date row from the table ?

sunny_talwar — Sat, 08 Oct 2016 07:28:18 GMT

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;

Re: how to load max date row from the table ?

sunny_talwar — Sat, 08 Oct 2016 07:33:18 GMT

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;

Re: how to load max date row from the table ?

gauravgg — Sat, 08 Oct 2016 08:07:19 GMT

thanks sunny

Re: how to load max date row from the table ?

timrogers13 — Wed, 28 Feb 2018 14:50:21 GMT

Thanks Sunny. This helped me out. -Tim

Re: how to load max date row from the table ?

kfloresc — Mon, 28 Sep 2020 14:06:13 GMT

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