Distinct census with overlapping dates

[#1]

Table ABC:

Employee	Department	StartDate	EndDate
Roger	Dept A	2/1/2012	2/5/2012
Roger	Dept B	2/5/2012	2/20/2012

I'm looking to get the census by Department. The problem I have is the start date of the first row is overlapping the end date of the first row. So when using the intervalmatch() function to tie the dates to the employee I get following:

               2/1/2012................2/5/2012..............2/20/2012

Dept A     Roger     Roger      Roger

Dept B                                 Roger                    Roger

------------------------------------------------------------------------------------------------------

Total           1          1               2                         1

The census total should always be 1/day.

Dept A census should be 1 from 2/1/2012 to 2/4/2012 and Dept B census is 1 from 2/5 to 2/20.

Does anyone know how to handle this? Any help is appreciated.

[swuehl · Accepted solution]

Maybe like this:

INPUT:

LOAD Employee,

     Department,

     StartDate,

     EndDate

FROM

[http://community.qlik.com/thread/51964?tstart=0]

(html, codepage is 1252, embedded labels, table is @1);

RESULT:

NOCONCATENATE LOAD Employee, Department, StartDate,

if(Employee=peek(Employee) and EndDate=peek(StartDate),Date(EndDate-1),EndDate) as EndDate

resident INPUT order by Employee, StartDate desc;

drop table INPUT;

It will order your table by Employee and StartDate descending, i.e. backwards in time.

Then check in every line if the Employee is identical to the previous line and if the EndDate is identical to the StartDate of the previous line (remember, previous line is forward in time). Then correct the EndDate by subtracting one day.

See also attached,

Stefan

[#3]

Thanks Stefan! It worked...