topic Re: Tracking Of Employee in QlikView

Tracking Of Employee

Anonymous — Tue, 17 Jun 2014 13:00:17 GMT

Hello,

I am having a table of employee record with following fields(say):

EmpName, DeptName, EntryDate, Location

Now I have to display the list of employee details with department/Location wise.

My problem is what, say, a employee changing a department with location and then again he/she comes back to same department/location or remains their only. Now I have to display the list department/location wise but I am getting repeating employee record into different department/location.

Example: Say ABC, employee was into Finance department with EntryDate and then he/she moves to Sales department with EntryDate(Last updated). Now If I display the list he/she will come into both the department as their no over write on earlier record.

I am maintaining EntryDate as per his/her last changed department.

I tried to fetch the list from department/location wise with max(EntryDate) but its not working.....

Can any body help, how I can sort it out?

List should be according to last EntryDate and employee records should not be duplicate.

Thanks,

Balraj Ahlawat

Re: Tracking Of Employee

giakoum — Tue, 17 Jun 2014 13:03:38 GMT

use the firstsortedvalue function

Re: Tracking Of Employee

Anonymous — Tue, 17 Jun 2014 13:15:57 GMT

Ioannis,

Could you tell me syntax please, how to use it?

Regards,

Balraj Ahlawat

Re: Tracking Of Employee

giakoum — Tue, 17 Jun 2014 13:31:11 GMT

use -[yourdateField] to display only the last value :

firstsortedvalue ([ distinct ] expression [, sort-weight [, n ]])

Returns the first value of expression sorted by corresponding sort-weight when expression is iterated over a number of records as defined by a group by clause. Sort-weight should return a numeric value where the lowest value will render the corresponding value of expression to be sorted first. By preceding the sort-value expression with a minus sign, the function will return the last value instead. If more than one value of expression share the same lowest sort-order, the function will return NULL. By stating an n larger than 1, the nth value in order will be returned. If the word distinct occurs before the expression, all duplicates will be disregarded.

Example:

Load Customer,

firstsortedvalue(PurchasedArticle, OrderDate) as FirstProductBought from abc.csv

group by Customer;

Re: Tracking Of Employee

venkatg6759 — Tue, 17 Jun 2014 13:33:33 GMT

=Firstsortedvalue(Department,-EntryDate)

Re: Tracking Of Employee

Tue, 17 Jun 2014 13:40:27 GMT

Attached example that refer to your issue

find the solution in the script