Script Help to pick only max(Effect_date)

[Anonymous]

Hello,

I need  some help to fix my script. How to get the max(date) per person record. On my third I still multiple record per person after group by ?

Thanks !!

New-qlikview user

First Tab:

EmplHist1:
LOAD EMPL_ID,
  FULL_NAME,
  HOME_ORG_ID,
  TITLE_DESC,
  GLC,
  STATUS,
  DATE(EFFECTIVE_DATE) as [EMP_EFFECT_DATE1],
  DATE(EMP_END_DATE) as [END_DATE]
 
RESIDENT TempEmployeeHist
WHERE EXISTS (DirectOrg, HOME_ORG_ID) ;

STORE EmplHist1 INTO ..\..\abc2\qvds\emplhist.qvd;

DROP TABLE  EmplHist1;

Second Tab:

//***********************
PostDate:
LOAD EMPL_ID,
  DATE(MAX(EFFECT_DATE)) as [EMP_EFFECT_DATE2]
 
RESIDENT PostDate1
GROUP BY EMPL_ID;

STORE PostDate INTO ..\..\abc2\qvds\PostDate.qvd;

DROP TABLE PostDate;

Third Tab:

//******************

EEHist:

LOAD EMPL_ID,
     FULL_NAME,
     HOME_ORG_ID,
     TITLE_DESC,
     GLC,
     STATUS,
     DATE(MAX(EMP_EFFECT_DATE1)) as [EFFECT_DATE]
    
FROM
..\qvds\emplhist.qvd
(qvd)
GROUP BY EMPL_ID, FULL_NAME, HOME_ORG_ID, TITLE_DESC, GLC, STATUS;

LEFT JOIN (EEHist)

LOAD EMPL_ID,
     EMP_EFFECT_DATE2 as [EFFECT_DATE]
FROM
..\qvds\PostDate.qvd
(qvd);

[chematos]

have you tried Max(Date(EMP_EFFECT_DATE1)) ??

Group by seems ok.