timeline count of employees

[amien]

in a qvw i have two tables:

1. master calander. Generates a calanders with all days and months

2. an basic INLINE script with employee data

name, day_in, day_ out

user1, 20050106, 20070306

user2, 20090205,

user3, 20060807, 20090801

etc (list of 1000 employees)

i want to know for each day and month how many employees are employed. So if its a pivot for a month, user1 will be counted for 200501 till 200703. user2 will be counter for 200902 until last month in master calander etc.

i need a third table contains this:

date, month, # of availible employees

hope its clear 🙂

[amien]

added example qvw

[#3]

Hello

If I would need to do it in the script I would do something like below. I performance is an issue I would think about a solution using 'sort by' instead of 'group by'. If you dont need it in the script or make employees by date searchable in front-end I believeit to be easier to use a graph to calculate this.

Regards

//Nils

a:
noconcatenate load date#(MasterClander_Date,'YYYYMMDD') as MasterClander_Date resident xxx;


b:
load date#(day_in,'YYYYMMDD') as MasterClander_Date // Didnt really get what start date you would like to use. alt. ~MonthStart(day_in) as MasterClander_Date
 ,'1' as Employee_flag
resident yyy;

load date#(day_out,'YYYYMMDD')+1 as MasterClander_Date // alt. ~MonthEnd(day_out)+1 as MasterClander_Date
 , '-1' as Employee_flag
resident yyy
where len(day_out)>0;


c:
load MasterClander_Date, sum(Employee_flag) as Employee_Change
resident b
group by MasterClander_Date;


d:
noconcatenate load * resident a;
join load * resident c;

load MasterClander_Date, numsum(Employee_Change,peek('Employees')) as Employees
resident d;

drop tables ...;

[#4]

Amien,

IntervalMatch is designed to solve your problem. Take a look at the attached. Note that you have to convert your inline dates to QlikView dates.

[amien]

Thanks both for the reply..

@Matt .. this is really a clean solution .. date format should be allways YYYYMMDD .. so i have changed

SET DateFormat='YYYYMMDD';

and

dropped the rewriting of the dates..

but now datein and dateout look like this : 28-4-55454 and 17-8-54454 .. see attachment

thanks in advanced