Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for Data Integration and Data Analytics gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Master III
Master III

counting employee by comparing date with system-date

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

21 Replies
Highlighted
Master III
Master III

Re: counting employee by comparing date with system-date

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;

Highlighted
Master III
Master III

Re: counting employee by comparing date with system-date

hi

the MEMBER_EXIT_DATE comparison with system-date is correct?

Highlighted
Master III
Master III

Re: counting employee by comparing date with system-date

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

Highlighted
MVP
MVP

Re: counting employee by comparing date with system-date

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



Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Highlighted
Specialist
Specialist

Re: counting employee by comparing date with system-date

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

Highlighted
Master III
Master III

Re: counting employee by comparing date with system-date

hi Jonathan,

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

Highlighted
MVP
MVP

Re: counting employee by comparing date with system-date

Opps - you are quite right!

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Highlighted
Master III
Master III

Re: counting employee by comparing date with system-date

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

Highlighted
MVP
MVP

Re: counting employee by comparing date with system-date

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein