Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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;
Thanks for the response, but I need to Group By only CustID
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.
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;