Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
hi
try to replace the right join with the inner join
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.