Need to help for Max date error

[Anonymous]

I got this error message from reload my script

invalid expression

EmplHist:

LOAD EMPL_ID,

  FULL_NAME,

  Status,

  HOME_ORG_ID,

  TITLE_DESC,

  GLC,

  DATE(max([EMP_END_DATE]))

RESIDENT TempEmployeeHist

WHERE EXISTS (DirectOrg, HOME_ORG_ID)

=================================

TempEmployeeHist:
LOAD   EMPL_ID,
    LAST_NAME & ', '& FIRST_NAME as [FULL_NAME],
    S_EMPL_STATUS_CD as Status;
   
SQL SELECT *
FROM $(vSchemaOwner).EMPL;

LEFT JOIN (TempEmployeeHist)
LOAD   EMPL_ID,
    ORG_ID as [HOME_ORG_ID],
    TITLE_DESC,
    GENL_LAB_CAT_CD as [GLC],
    DATE(END_DT) as [EMP_END_DATE];
SQL SELECT *
FROM $(vSchemaOwner).EMPL_LAB_INFO;

//***********************
EmplHist:
LOAD EMPL_ID,
  FULL_NAME,
  Status,
  HOME_ORG_ID,
  TITLE_DESC,
  GLC,
  DATE(max([EMP_END_DATE]))
 
RESIDENT TempEmployeeHist
WHERE EXISTS (DirectOrg, HOME_ORG_ID);

//DROP TABLE TempEmployeeHist;

[MayilVahanan]

HI,

     When you aggregation function like max(), you want to use group by..

For ex:

Load A, B, C , Max(D) as D from tablename group by A,B,C;

Hope it helps