counting employee by comparing date with system-date

[arulsettu]

hi,

    i am trying to count policy for that i did count(employee id). it showing all the policies including expired also.

what i need is i need to compare the date like

expired date>=system date. then it will show only active policy.

for that i did in backend

test:

LOAD

EMPLOYEE_POL_ID as EMPLOYEE_POL_ID_resid,

PLAN_ID as PLAN_ID_resid,

EMPLOYEE_ID,

MEMBER_EXIT_DATE as MEMBER_EXIT_DATE_resid,

if(date(MEMBER_EXIT_DATE)>=date(Today()),count(EMPLOYEE_ID)) as new_id

Resident EMPLOYEE Group by EMPLOYEE_POL_ID,PLAN_ID,EMPLOYEE_ID;

while loading it shows error in expression.

can anyone tell me what i am doing wrong

Thanks

[sasiparupudi1]

LOAD

EMPLOYEE_POL_ID as EMPLOYEE_POL_ID_resid,

PLAN_ID as PLAN_ID_resid,

EMPLOYEE_ID,

MEMBER_EXIT_DATE as MEMBER_EXIT_DATE_resid,

if(date(MEMBER_EXIT_DATE)>=date(Today()),count(EMPLOYEE_ID)) as new_id

Resident EMPLOYEE

Group by EMPLOYEE_POL_ID,PLAN_ID,EMPLOYEE_ID,MEMBER_EXIT_DATE;

[arulsettu]

hi

the MEMBER_EXIT_DATE comparison with system-date is correct?

[sasiparupudi1]

what is the format of your date? is it a date or a string?

[jonathandienst]

You need MEMBER_EXIT_DATE in the Group By as already explained.

I think that the new_id line should read like one of these:

Count(if(MEMBER_EXIT_DATE >= Today(), EMPLOYEE_ID)) as new_id

Count({<MEMBER_EXIT_DATE = {">=$(=Today())"}>}, EMPLOYEE_ID) as new_id

[SreeniJD]

Hi Arul

Its correct comparision! Alternatively you can have another column as policy status (Active,Inactive) for those who expired date is less than or greater than today()..

HTH,

Sreeni

[arulsettu]

hi Jonathan,

second one is set analysis so it is not possible in backend right?

[jonathandienst]

Opps - you are quite right!

[arulsettu]

my comparison with system date is correct? because it is showing wrong values

[jonathandienst]

Is MEMBER_EXIT_DATE being read as a date (numeric) or string. If you create a list box, the values will right align if they are numeric.

If they are strings, then you need to convert them to dates for the comparison to work:

LOAD *,

  if(date(MEMBER_EXIT_DATE)>=date(Today()),count(EMPLOYEE_ID)) as new_id

;

LOAD

  EMPLOYEE_POL_ID as EMPLOYEE_POL_ID_resid,

  PLAN_ID as PLAN_ID_resid,

  EMPLOYEE_ID,

  Date(Date#(MEMBER_EXIT_DATE, 'YYYY.MM.DD')) as MEMBER_EXIT_DATE_resid

Resident EMPLOYEE

Group by

  EMPLOYEE_POL_ID,

  PLAN_ID,

  EMPLOYEE_ID,

  MEMBER_EXIT_DATE

;

The bold text should be the format used in MEMBER_EXIT_DATE. The preceding load means that we only need to do the conversion once.