Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Script Help to pick only max(Effect_date)


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);

1 Reply
chematos
Specialist II
Specialist II

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

Group by seems ok.