Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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);
have you tried Max(Date(EMP_EFFECT_DATE1)) ??
Group by seems ok.