topic Re: counting employee by comparing date with system-date in QlikView

counting employee by comparing date with system-date

arulsettu — Wed, 30 Sep 2015 07:00:23 GMT

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

Re: counting employee by comparing date with system-date

sasiparupudi1 — Wed, 30 Sep 2015 07:15:14 GMT

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;

Re: counting employee by comparing date with system-date

arulsettu — Wed, 30 Sep 2015 07:22:51 GMT

the MEMBER_EXIT_DATE comparison with system-date is correct?

Re: counting employee by comparing date with system-date

sasiparupudi1 — Wed, 30 Sep 2015 07:25:23 GMT

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

Re: counting employee by comparing date with system-date

jonathandienst — Wed, 30 Sep 2015 07:30:19 GMT

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

Re: counting employee by comparing date with system-date

SreeniJD — Wed, 30 Sep 2015 07:30:52 GMT

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

Re: counting employee by comparing date with system-date

arulsettu — Wed, 30 Sep 2015 07:34:55 GMT

hi Jonathan,

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

Re: counting employee by comparing date with system-date

jonathandienst — Wed, 30 Sep 2015 07:38:48 GMT

Opps - you are quite right!

Re: counting employee by comparing date with system-date

arulsettu — Wed, 30 Sep 2015 07:41:17 GMT

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

Re: counting employee by comparing date with system-date

jonathandienst — Wed, 30 Sep 2015 07:47:11 GMT

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.

Re: counting employee by comparing date with system-date

jonathandienst — Wed, 30 Sep 2015 07:49:36 GMT

Re: counting employee by comparing date with system-date

tresB — Wed, 30 Sep 2015 07:50:40 GMT

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

Re: counting employee by comparing date with system-date

arulsettu — Wed, 30 Sep 2015 07:52:46 GMT

format like this

Re: counting employee by comparing date with system-date

sasiparupudi1 — Wed, 30 Sep 2015 07:57:11 GMT

Try

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

Re: counting employee by comparing date with system-date

krishna20 — Wed, 30 Sep 2015 07:58:48 GMT

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;

Re: counting employee by comparing date with system-date

arulsettu — Wed, 30 Sep 2015 08:15:31 GMT

HI Tresesco

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

Re: counting employee by comparing date with system-date

arulsettu — Wed, 30 Sep 2015 08:22:44 GMT

Jonathan

it says aggr should come with group by

Re: counting employee by comparing date with system-date

tresB — Wed, 30 Sep 2015 08:30:31 GMT

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

Re: counting employee by comparing date with system-date

arulsettu — Wed, 30 Sep 2015 08:40:01 GMT

i will prepare the sample

Re: counting employee by comparing date with system-date

arulsettu — Wed, 30 Sep 2015 09:17:26 GMT

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

any idea?