Split records - clock items per interval

[#1]

Hi QlikView Guru's,

I am wondering how i can split and calculate records in my load script.

Example:

I have two records like this:

date	time start	time end	total seconds	employee ID
2012-12-01	09:15:00	11:15:00	7200	101
2012-12-01	10:20:00	12:20:00	7200	265

After loading this record I want this as output

date	time start	time end	total seconds	employee ID
2012-12-01	09:15:00	10:00:00	2700	101
2012-12-01	10:00:00	11:00:00	3600	101
2012-12-01	11:00:00	11:15:00	900	101
2012-12-01	10:20:00	11:00:00	2400	265
2012-12-01	11:00:00	12:00:00	3600	265
2012-12-01	12:00:00	12:20:00	1200	265

This way I can sum the total seconds per time interval.

It would be great if someone can solve this puzzle.

Thanks a lot for helping me out.

Greetings, Dave

[Gysbert_Wassenaar · Accepted solution]

See attached qvw (adapted from this solution, credits due swuehl)

[#3]

Thanks Gysbert,

This is really great! I am able to continu my dashboard .

Also thanks to Swuehl.

Greetings, Dave