Load Data of the latest date

[aslam0316_bi]

Hello Team,

I want to load the data of the latest date, I have tried the below method by seeing some other solution but its not working for me, can anyone please help me here?

Table1:
LOAD id, season, city, Date(date, 'DD/MM/YYYY') as date Inline [
id, season, city , date
1, 2017, Hyderabad, 05/01/2017
2, 2017, Pune, 06/01/2017
3, 2017, Rajkot, 07/01/2017
4, 2017, Indore, 08/01/2017
];

MaxTable:
NoConcatenate
Load
id,
Max(Date(date'DD/MM/YYYY')) as newdate
Resident Table1;

Drop Table Table1;

I have attached the error screenshot, Any guidance will be much appreciated 🙂

[rubenmarin1 · Accepted solution]

Hi, when you use an aggregation expression lke MAx you need to add a group by clause especifying all field not included in an aggregation expression, so in that case:

MaxTable:
NoConcatenate
Load
id,
Max(Date(date'DD/MM/YYYY')) as newdate
Resident Table1
Group By id;

But this won't work if you want to keep only the row with the max date, as it will return the max date for each id.

To keep only one row you can use an inner join to reduce data to the max date:

Table1:
LOAD id, season, city, Date(date, 'DD/MM/YYYY') as date Inline [
id, season, city , date
1, 2017, Hyderabad, 05/01/2017
2, 2017, Pune, 06/01/2017
3, 2017, Rajkot, 07/01/2017
4, 2017, Indore, 08/01/2017
];

Inner Join (Table1)
Load
Date(Max(date),'DD/MM/YYYY') as date
Resident Table1;

[aslam0316_bi]

Great! Thanks a lot, you are genius.