How to load only the max date record of individual row

[vikasshana]

Hi,

I've below table.

ID	Name	Reason	Test	Date
1	A1	Failed	NA	25/02/2021
1	A1	Failed	NA	24/02/2021
1	A2	Failed	-	07/01/2021
1	A1	Not Failed	-	07/01/2021
2	A3	Success	NA	22/02/2021
2	A3	Success	NA	21/02/2021
2	A4	Failed	NA	06/01/2021

Looking for a load script to load only those records which has max date for each ID

expected output

ID	Name	Reason	Test	Date
1	A1	Failed	NA	25/02/2021
2	A3	Success	NA	22/02/2021

Tried below script and is not working as expected.

Table:

LOAD

    ID,

    Name,

   Reason,

   Date,

   Test

FROM (****);

Right Join (Table)

LOAD ID,

  Max(Date) as Date

Resident Table

Group by ID;

[MendyS]

hi 

try to replace the right join with the inner join

[justISO]

Your script should work with right join too, but I believe in your case there are not the same Date formats so it works not as expected. Try to change accordingly to 'date(Date) as Date' in first table and 'Max(date(Date)) as Date' in resident table.