Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
arulsettu
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
sasiparupudi1
Master III
Master III

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
Master III
Master III
Author

hi

the MEMBER_EXIT_DATE comparison with system-date is correct?

sasiparupudi1
Master III
Master III

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



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

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
Master III
Master III
Author

hi Jonathan,

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

jonathandienst

Opps - you are quite right!

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

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.

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