topic Re: Split StartDate and EndDate in monthly view in QlikView

Split StartDate and EndDate in monthly view

salto — Mon, 13 Apr 2015 15:05:50 GMT

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.

Re: Split StartDate and EndDate in monthly view

Mon, 13 Apr 2015 15:22:46 GMT

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

Re: Split StartDate and EndDate in monthly view

Anonymous — Mon, 13 Apr 2015 16:19:21 GMT

See the attached.

Re: Split StartDate and EndDate in monthly view

maxgro — Mon, 13 Apr 2015 20:51:23 GMT

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;

Re: Split StartDate and EndDate in monthly view

salto — Tue, 14 Apr 2015 10:03:28 GMT

Hi,

this is exactly what I was looking for.

Many thanks!

Re: Split StartDate and EndDate in monthly view

salto — Tue, 14 Apr 2015 10:04:19 GMT

Hi Massimo,

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