Script Help to pick only max(Effect_date)

Anonymous — Wed, 25 Jul 2012 16:51:47 GMT

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

Re: Script Help to pick only max(Effect_date)

chematos — Thu, 26 Jul 2012 20:53:20 GMT

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

Group by seems ok.

topic Re: Script Help to pick only max(Effect_date) in QlikView

Script Help to pick only max(Effect_date)

Re: Script Help to pick only max(Effect_date)