Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Sum employee sick days excluding the days he was sick in a following year

Hello,

I'm new to qlikview (using the personal edition) but im going to try to be clear as possible about my problem. I'm using a table from an oracle database, here a part of it as an example:

ID_PERID_WGVDATESICKDATEBETTER
240115-12-1998 0:003-1-1999 0:00
41131-12-1998 0:0011-4-1999 0:00
70129-12-1998 0:006-1-1999 0:00
224121-12-1998 0:003-1-1999 0:00
13130-12-1998 0:003-1-1999 0:00

The third column show the day an employee is sick and the fourth column show the day an employee is better (this day counts as a sick day, the employee star working again on the next day). I created a table object with the following colums:

Year

Month

Date

(I loaded these columns as seperate from the DATESICK column. Then i created the following expression SUM (DATEBETTER - DATESICK+ 1), this will get the number of days an employee was sick in a certian period.

The problem here is that sometimes an employee is sick in, for example, 1998 and gets better in 1999.

If i click on the year 1998 in my table object it shows the number of days the employees where sick including the days they were sick in the beginning of 1999. I want that my table object only shows the Sick days of a certain year.

Is this possible and how? I hope that u can understand my problem. If u have question or need more information about my problem just tell me. 🙂

1 Solution

Accepted Solutions
Gysbert_Wassenaar

Dang, I read that before getting started and thought that a smart thing to mention. And then I post a qvw file anyway... duh!

Anyway, try the script below. You'll get a YEARSICKTIME and a YEAR field so that you can sum the sicktime per year.

 

T1:
LOAD
ID_PER, ID_WGV,
date#(DATESICK,'DD-MM-YYYY hh:mm') as DATESICK,
date#(DATEBETTER,'DD-MM-YYYY hh:mm') as DATEBETTER,
year(date#(DATESICK,'DD-MM-YYYY hh:mm')) as YEARSICK,
year(date#(DATEBETTER,'DD-MM-YYYY hh:mm')) as YEARBETTER
  INLINE [
    ID_PER, ID_WGV, DATESICK, DATEBETTER
    54, 1, 01-12-1998 00:00, 04-12-1998 00:00
    46, 1, 03-12-1998 00:00, 06-12-1998 00:00
    156, 1, 05-12-1998 00:00, 25-12-1998 00:00
    65, 1, 12-12-1998 00:00, 16-12-1998 00:00
    41, 1, 14-12-1998 00:00, 23-12-1998 00:00
    240, 1, 15-12-1998 00:00, 03-01-1999 00:00
    41, 1, 31-12-1998 00:00, 11-04-1999 00:00
    70, 1, 29-12-1998 00:00, 06-01-1999 00:00
    224, 1, 21-12-1998 00:00, 03-01-1999 00:00
    13, 1, 30-12-1998 00:00, 03-01-1999 00:00
    85, 1, 02-01-1999 00:00, 05-01-1999 00:00
    134, 1, 05-01-1999 00:00, 12-01-1999 00:00
    241, 1, 06-01-1999 00:00, 09-01-1999 00:00
    12, 1, 11-01-1999 00:00, 01-02-1999 00:00
];

T2:
load *,
YEARSICK+IterNo()-1 as YEAR,
if(YEARSICK=YEARBETTER,DATEBETTER-DATESICK+1,
  if(YEARSICK=YEARSICK+IterNo()-1,
     YearEnd(DATESICK)-DATESICK,
     DATEBETTER-YearStart(DATEBETTER)+1)) as YEARSICKTIME
resident T1 while YEARSICK+IterNo()-1<=YEARBETTER;

drop table T1;

 

 


talk is cheap, supply exceeds demand

View solution in original post

10 Replies
Gysbert_Wassenaar

See attached file


talk is cheap, supply exceeds demand
Not applicable
Author

Thanks for your reaction but i can't open the QVX file because im using the free personal edition of qlikview.

Gysbert_Wassenaar

Dang, I read that before getting started and thought that a smart thing to mention. And then I post a qvw file anyway... duh!

Anyway, try the script below. You'll get a YEARSICKTIME and a YEAR field so that you can sum the sicktime per year.

 

T1:
LOAD
ID_PER, ID_WGV,
date#(DATESICK,'DD-MM-YYYY hh:mm') as DATESICK,
date#(DATEBETTER,'DD-MM-YYYY hh:mm') as DATEBETTER,
year(date#(DATESICK,'DD-MM-YYYY hh:mm')) as YEARSICK,
year(date#(DATEBETTER,'DD-MM-YYYY hh:mm')) as YEARBETTER
  INLINE [
    ID_PER, ID_WGV, DATESICK, DATEBETTER
    54, 1, 01-12-1998 00:00, 04-12-1998 00:00
    46, 1, 03-12-1998 00:00, 06-12-1998 00:00
    156, 1, 05-12-1998 00:00, 25-12-1998 00:00
    65, 1, 12-12-1998 00:00, 16-12-1998 00:00
    41, 1, 14-12-1998 00:00, 23-12-1998 00:00
    240, 1, 15-12-1998 00:00, 03-01-1999 00:00
    41, 1, 31-12-1998 00:00, 11-04-1999 00:00
    70, 1, 29-12-1998 00:00, 06-01-1999 00:00
    224, 1, 21-12-1998 00:00, 03-01-1999 00:00
    13, 1, 30-12-1998 00:00, 03-01-1999 00:00
    85, 1, 02-01-1999 00:00, 05-01-1999 00:00
    134, 1, 05-01-1999 00:00, 12-01-1999 00:00
    241, 1, 06-01-1999 00:00, 09-01-1999 00:00
    12, 1, 11-01-1999 00:00, 01-02-1999 00:00
];

T2:
load *,
YEARSICK+IterNo()-1 as YEAR,
if(YEARSICK=YEARBETTER,DATEBETTER-DATESICK+1,
  if(YEARSICK=YEARSICK+IterNo()-1,
     YearEnd(DATESICK)-DATESICK,
     DATEBETTER-YearStart(DATEBETTER)+1)) as YEARSICKTIME
resident T1 while YEARSICK+IterNo()-1<=YEARBETTER;

drop table T1;

 

 


talk is cheap, supply exceeds demand
Not applicable
Author

Thanks for yor reaction but this is not excactly what i had in mind. Now i have to manually load the data (which are many, many rows of sick dates from 1998 till now), can't i make it work with some kind of expression or such?

Gysbert_Wassenaar

Manually load?? I don't understand what you mean. Don't you load data now too? The inline load is just an example to have some data for demonstration purposes. The T2 table is where the action happens. It just needs an input table to make things happen. That doesn't have to be an inline load. Anything (sql select, xlsx, txt,csv, xml, qvd etc) will do as long as the date fields are actually dates and not text. And change field names in T2 to match the field names in your source table.


talk is cheap, supply exceeds demand
Not applicable
Author

Oops my bad, i saw it wrong. Gonna try it again on monday when i have acces to my Qlikview application and databases, i will let you know how it went then.

Not applicable
Author

I tried your script today but its giving me the following empty columns, YEARSICK, YEARBETTER, YEARSICKTIME and YEAR are empty. I removed the Inline part and add an SQL Select statement, this was the exact script i used:

OLEDB CONNECT TO [....]

LOAD

   
DATEZIEK,

   
Year (DATEZIEK) as DepYear,

    
Month (DATEZIEK) as DepMonth,

    
Day (DATEZIEK) as DepDay,

    
Time (DATEZIEK) as DepTime;

SQL SELECT DATIZIEK

FROM PRODPIM."PIM_TZKT";

T1:
LOAD

ID_PER, ID_WGV,

date#(DATESICK,'DD-MM-YYYY hh:mm') as DATESICK,

date#(DATEBETTER,'DD-MM-YYYY hh:mm') as DATEBETTER,

year(date#(DATESICK,'DD-MM-YYYY hh:mm')) as YEARSICK,

year(date#(DATEBETTER,'DD-MM-YYYY hh:mm')) as YEARBETTER

;


SQL SELECT ID_PER, ID_WGV, DATESICK, DATEBETTER
FROM PRODPIM."PIM_TZKT";

T2:
load *,
YEARSICK+IterNo()-1 as YEAR,
if(YEARSICK=YEARBETTER,DATEBETTER-DATESICK+1,
  if(YEARSICK=YEARSICK+IterNo()-1,
     YearEnd(DATESICK)-DATESICK,
     DATEBETTER-YearStart(DATEBETTER)+1)) as YEARSICKTIME
resident T1 while YEARSICK+IterNo()-1<=YEARBETTER;

Drop table T1;

SQL SELECT *
FROM PRODPIM."PIM_TZKT";

Do you know what i did wrong?

(Im also dutch by the way, dont know if only english is allowed on this forum)

Gysbert_Wassenaar

Make sure you use the exact same field names (QV is case sensitive too). If your database doesn't have a field named DATESICK, but it does have a field DATEZIEK, then use DATEZIEK everywhere in place of DATESICK. Same for DATEBETTER.

SQL SELECT DATIZIEK          <-- Typo? DATIZIEK or DATEZIEK?

FROM PRODPIM."PIM_TZKT";

SQL SELECT ID_PER, ID_WGV, DATESICK, DATEBETTER <-- Does your table have fields with these names?
FROM PRODPIM."PIM_TZKT";


talk is cheap, supply exceeds demand
Not applicable
Author

I did, im sure i have the same field names now and there no typo's anymore  but im still getting empty columns.

Very strange.