topic complex aggregation in QlikView

complex aggregation

Thu, 23 Feb 2012 06:01:34 GMT

i have a table with all data for an employee like his hiring, redesignation , leaving with two dates maintained for each record

Employee	Start Date	EndDate	Status	Action
A	26-08-1990	10-04-2011	Active	Hiring
A	11-04-2011	29-10-2011	Active	Redesignation
A	30-10-2011	31-12-9999	Withdrawn	Leaving
B	01-01-2011	31-12-9999	Active	Hiring
C	30-10-2002	31-12-2003	Active	Hiring
C	01-01-2004	31-12-9999	Active	Redesignation

i want to calculate duration of service for each employees.

How to achieve this ?

complex aggregation

tresB — Thu, 23 Feb 2012 06:13:35 GMT

Can the logic be like - (Max of EndDate - Min of StartDate) after grouping Dates on Employee?

Is this what you want?

Regards, tresesco

Re: complex aggregation

jagan — Thu, 23 Feb 2012 06:14:33 GMT

Hi,

Use the script below

Arrive a new table for calculating days serviced.

LOAD

Employee,

Max(Num(EndDate)) - Min(Num(StartDate)) AS DaysServiced

FROM DataSource

GROUP BY Employee;

Hope this helps you

Regards,

Jagan.

Re: complex aggregation

jagan — Thu, 23 Feb 2012 06:19:41 GMT

Hi,

Use the script below

LOAD

Employee,

EndDate,

StartDate,

Status,

Action,

Num(EndDate) - Num(StartDate) AS DaysServiced

FROM DataSource

GROUP BY Employee;

Hope this helps you

Regards,

Jagan.

complex aggregation

vijay_iitkgp — Thu, 23 Feb 2012 06:23:37 GMT

Can you please let us know what should be duration for Employee A.

Re: complex aggregation

Thu, 23 Feb 2012 06:31:47 GMT

the duration of employee A should be (start date when action is leaving - start date when action is hiring) i.e 30-10-2011 - 26-08-

and if the employee is still active till the last record his duration sho1990uld be calculated from today - start date of hiring

Re: complex aggregation

Thu, 23 Feb 2012 06:37:58 GMT

thanks jagan
but do u think that there should be some check on status so that the right value is calculated because the end date is not showing the correct picture , employee hiredate and enddate is maintained in startdate attribute

complex aggregation

vijay_iitkgp — Thu, 23 Feb 2012 06:46:32 GMT

Hi ,

You can use this Code:

Table1:

Load

Employee,

StartDate as HiringDate

From Table Where Action=Hiring;

Left Join

Load

Employee,

Date(Max(StartDate),'DD-MM-YYYY') as LatestDate

From Table Group By Employee;

Left Join

Load

Employee,

Date(StartDate,'DD-MM-YYYY') as LatestDate,

Status,

Action

From Table Group;

Table2:

Noconcatenate

Load

If(Status=Active,Today-HiringDate,Action='Leaving',LatestDate-HiringDate) as Age

Resident Table1;

Drop Table1;

Hope this will help

Re: complex aggregation

CELAMBARASAN — Thu, 23 Feb 2012 06:59:37 GMT

Hi,

Try with this

Temp:

Load * Inline

[

Employee,StartDate,EndDate,Status,Action

A,26/08/1990,10/04/2011,Active,Hiring

A,11/04/2011,29/10/2011,Active,Redesignation

A,30/10/2011,31/12/9999,Withdrawn,Leaving

B,01/01/2011,31/12/9999,Active,Hiring

C,30/10/2002,31/12/2003,Active,Hiring

C,01/01/2004,31/12/9999,Active,Redesignation];

serviceDateTable:

Load

Employee,

Min(StartDate) as ServiceStart,

Max(StartDate) as ServiceEnd

Resident

Temp

Group by Employee;

inner Join(serviceDateTable)

Load

Employee,

StartDate as ServiceEnd,

Status as CurrentStatus

Resident

Temp;

Left Join(serviceDateTable)

Load

Employee,

if(CurrentStatus='Active',interval(Today() - ServiceStart,'D'),interval(ServiceEnd-ServiceStart,'D')) as ServiceDays

Resident

serviceDateTable;

Hope it helps

Celambarasan

complex aggregation

jagan — Thu, 23 Feb 2012 07:04:01 GMT

Hi,

Try below script,

TempTable:

Load

Employee,

StartDate as HiringDate

From Table Where Action='Hiring';

Left Join

Load

Employee,

StartDate as LeavingDate

From Table Where Action='Leaving';

Data:

Load

Employee,

If(IsNull(LeavingDate), Num(Today()) - Num(HiringDate), Num(LeavingDate) - Num(HiringDate) AS ServiceDays

RESIDENT TempTable;

DROP TABLE TempTable;

Hope this helps you.

Regards,

Jagan.