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

[jonathandienst]

[tresesco]

That could be because of date field value. Load the date field separately like:

Load

          date(MEMBER_EXIT_DATE) as Date,

          MEMBER_EXIT_DATE

From <>;

Do you get proper dates in Date field ? Check in the front-end. You might have to format the date field using date#() or so like:

Load

          date(date#(MEMBER_EXIT_DATE, 'YYYY-MMM-DD')) as Date,   // change date format to suit your data

[arulsettu]

format like this

[sasiparupudi1]

Try

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

[krishna20]

Hi Arul,

What is format of your date field MEMBER_EXIT_DATE??? Is it in Time stamp format??

If yes then you need to use Now() in the script instead of today() and need to use Floor function.

Use this code..It should get your desired result.

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(Floor(MEMBER_EXIT_DATE)>=Floor(Now()),count(EMPLOYEE_ID)) as new_id

Resident EMPLOYEE Group by EMPLOYEE_POL_ID,PLAN_ID,EMPLOYEE_ID;

[arulsettu]

HI Tresesco

                    above is my date format. can you tell how to achieve this

[arulsettu]

Jonathan

it says aggr should come with group by

[tresesco]

Are you sure that there are dates greater than today in your date field ? Could you post a sample qvw?

[arulsettu]

i will prepare the sample

[arulsettu]

the date comparison is for check the policy expired or not. if it is expired we should not count.

any idea?