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 TempEmployeeHistWHERE 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 PostDate1GROUP 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.