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: 
wossenhh
Creator
Creator

how to create a list for hire and terminate employee

Hi,

I am trying to create a table to display a new hired employees and terminated for required departments.

For e.g. This report run every day to see if there are any hired or terminated employees for Department A, B and C out of 10 departments. I have the fields Employee name, Start_date, End_date.

My script looks like

Temp_Employee:

SQL SELECT *

FROM database;

Employee:

Load Name,

Department,

Start_Date,

End_Date

Resident Temp_Employee

Where (department = ‘A’) and

           (department = ‘B’) and

           (department = ‘C’);

Drop Table Temp_Employee;

I have dimensions Name, Department fields and the expression as

count(End_date = date(Today(),'MM/DD/YYYY')) for Terminated employee, and

count(Start_date = date(Today(),'MM/DD/YYYY')) for new hired employee

And I couldn't get a result.

Thanks,

Wossen

1 Solution

Accepted Solutions
alexandros17
Partner - Champion III
Partner - Champion III

At first the quey is wrong:

Employee:

noconcatenate

Load Name,

Department,

Start_Date,

End_Date

Resident Temp_Employee

Where (department = ‘A’) OR (department = ‘B’) OR (department = ‘C’);

Drop Table Temp_Employee;

Now the query return values.

Name and department are your dimension,

use this expression (for hìred):

Sum(If(Num(StartDate) = Num(Today()), 1,0))

let me know

View solution in original post

6 Replies
datagrrl
Creator III
Creator III

It should probably be Or instead of and for the where clause.

alexandros17
Partner - Champion III
Partner - Champion III

At first the quey is wrong:

Employee:

noconcatenate

Load Name,

Department,

Start_Date,

End_Date

Resident Temp_Employee

Where (department = ‘A’) OR (department = ‘B’) OR (department = ‘C’);

Drop Table Temp_Employee;

Now the query return values.

Name and department are your dimension,

use this expression (for hìred):

Sum(If(Num(StartDate) = Num(Today()), 1,0))

let me know

Not applicable

Hi,

Have a look at the attached appln, hope it helps.

Kiru

wossenhh
Creator
Creator
Author

Thanks Alessandro for your help! It works fine.

wossenhh
Creator
Creator
Author

Thanks Kiruthi! But I was trying to see the departments A, B, and C out of A ~J departments and also want to see people who hired and terminated day to day bases whenever QV runs.

wossenhh
Creator
Creator
Author

Hi Alessandro,

I just back with some question. Yesterday, on my excel file the end date was show 10/1/2014 and QV extract the employee list who has been hired as of October 1 correctly. And today I have run QV and the result shows the same as yesterday. I supposed to see nothing as of 10/2/2014 since I haven't update anything on my excel file.

Isn't the function Num(Today() to show today's date? Any idea?

Thanks,

Wossen