max date in load script

[karan_kn]

I need Max date for the Purchase date, but it should groupby only CustID. I'm invalid expression when I use the below script.

T1:

LOAD Distinct

Department,

CustID,

Item_Name,

Max(Date(Purchase_Date,'MM/DD/YYYY')) as Max_Date

Resident Temp Group by CustID;

DROP Table Temp;

[prma7799]

Have you tried this

T1:

LOAD Distinct

Department,

CustID,

Item_Name,

Max(Date(Purchase_Date,'MM/DD/YYYY')) as Max_Date

Resident Temp Group by CustID,Department;

DROP Table Temp;

[karan_kn]

Thanks for the response, but I need to Group By only CustID

[Peter_Cammaert]

Then remove all other fields, because for every unique CustomerID you will get a single Max Date, but possibly multiple Item_Names and they won't fit in a single row. Like:

T1:

LOAD CustID,

     Max(Date(Purchase_Date, 'MM/DD/YYYY')) AS Max_Date

RESIDENT Temp

GROUP BY CustID;

The idea is that all column names in the Load that do not appear in the GROUP BY clause, should be embedded in an aggregation function call, like Max(), Min(), Only(), FirstSortedValue(), Sum(), Avg() etc.

[prma7799]

Try this

T1:

LOAD 

CustID,

Item_Name,

Max(Date(Purchase_Date,'MM/DD/YYYY')) as Max_Date

Resident Temp Group by CustID;

DROP Table Temp;