Split StartDate and EndDate in monthly view

[salto]

Hello,

I have a table like this:

Employee	Contract_ID	StartDate	EndDate	Hours/day
A	C1	15/03/2010	20/03/2010	8
A	C2	24/03/2010	30/06/2010	6
B	C3	01/03/2010	04/05/2010	7

For a monthly analysis of the worked hours, I would like to convert it to a data-model like the one below, in which a monthly sum of worked hours is made for every employee:

Employee	Contract_ID	Year	Month	Hours
A	C1	2010	3	48 (result of 6 days * 8 hours/day)
A	C2	2010	3	42 (result of 7 days * 8 hours/day)
A	C2	2010	4	30*6
A	C2	2010	5	31*6
A	C2	2010	6	30*6
B	C3	2010	3	31*7
B	C3	2010	4	30*7
B	C3	2010	5	4*7

I would need to create a new record in the resulting table for every employee, contract_ID and month between the starting and ending date, including these dates' months.

Any help in this matter will be highly appreciated.

Many thanks in advance.

[#2]

Hi Salto,

maybe have a look at interval match IntervalMatch

should be able to use that to generate the required rows that you need

hope that helps

Joe

[Anonymous · Accepted solution]

See the attached.

[maxgro]

source:

LOAD Employee,

     Contract_ID,

     StartDate,

     EndDate,

     [Hours/day]

FROM

[https://community.qlik.com/thread/159862]

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

tmp:

load

  *,

  date(StartDate + IterNo() -1) as Date

Resident

  source

While StartDate + IterNo()-1 <= EndDate ;

final:

load Employee,

     Contract_ID,

     Year(Date) as y,

     Month(Date) as m,

     sum([Hours/day]) as [Hours/day]

resident tmp

group by  Employee,

     Contract_ID,

     Year(Date) ,Month(Date);

DROP Table tmp, source;

[salto]

Hi,

this is exactly what I was looking for.

Many thanks!

[salto]

Hi Massimo,

It does not exactly give me the result I was looking for, but many thanks for your script.