topic Re: Splitting the data in Work Hours & Days in QlikView

Splitting the data in Work Hours & Days

Thu, 01 May 2014 13:59:07 GMT

Hi,

I´ve trying this for several days, but I´m a newbie yet. I would like to split my data in work days.

For example. I´ll have:

Início do período (Start date) - 7/11/2013 11:10:00 am

Fim do período (End date) - 7/15/2013 10:20:00 am

I would like to have the interval of time excluding non working hours and sunday

7/11/2013 - consider only 11:10am to 6pm +

7/12/2013 - consider only 9am to 6pm

7/13/2013 - consider only 9am to 6pm

7/14/2013 whole 24hrs should be excluded

7/15/2013 consider only 2am to 10:20am

I tried this solution Splitting the data - On the way to granularity, but this is splitting in time zones. I could not make this in work days. I´ll need to transform this interval above ( 7/15/2013 10:20:00 am, 7/11/2013 11:10:00 am) in several days considering work hours)

Any help would be appreciated.

Regards

Fernando

Re: Splitting the data in Work Hours & Days

Anonymous — Thu, 01 May 2014 14:44:41 GMT

Fernando,

I tried something similar earlier this year. See if it gives you any helpful ideas.

Re: Splitting the data in Work Hours & Days

swuehl — Thu, 01 May 2014 15:16:12 GMT

Here is another discussion with script based and expression based aproaches:

Calculate hours between two Date/Time strings

Re: Splitting the data in Work Hours & Days

Thu, 01 May 2014 15:23:19 GMT

I appreciated but calculate the hours is solved. I need to split the timestamp in work hours.

Re: Splitting the data in Work Hours & Days

Thu, 01 May 2014 15:26:46 GMT

But your calcs is about to obtain work hours excluding the weekend correct? I appreciated, but the main problem now I´m dealing is split the timestamp. tks anyway

Re: Splitting the data in Work Hours & Days

Anonymous — Thu, 01 May 2014 15:31:12 GMT

My example is about both time and weekend.

Re: Splitting the data in Work Hours & Days

swuehl — Thu, 01 May 2014 15:33:24 GMT

The linked solution(s) should also handle timestamps and weekends (I remember even a solution handling breaks, but that was in another post).

Re: Splitting the data in Work Hours & Days

Thu, 01 May 2014 15:37:20 GMT

Sorry, but how are you dealing about the split? You have at source data 6 lines and your result is about the same 6 lines.

Re: Splitting the data in Work Hours & Days

Thu, 01 May 2014 15:45:06 GMT

Yes, but in those solutions they are solving calculate worked time. I´ve already calculated the worked time.

let´s assume, which is included in my example that I have

Start date:28/04/2014 15:31:00

End Date: 30/04/2014 08:43:00

I would like to have this result:

lState	ShiftID	TZStart	TZEnd
24	1	28/04/2014 15:31	28/04/2014 18:00
24	2	28/04/2014 09:00	29/04/2014 18:00
24	3	29/04/2014 09:00	29/04/2014 08:43

Regards

Fernando

Re: Splitting the data in Work Hours & Days

swuehl — Thu, 01 May 2014 15:56:02 GMT

Sorry, I am confused. Is this what you asked for in your initial post?

Re: Splitting the data in Work Hours & Days

Thu, 01 May 2014 16:02:19 GMT

Sorry I didn´t myselft clear. But yes, I would like to split the the interval (Start date:28/04/2014 15:31:00, End Date: 30/04/2014 08:43:00)

in work / days hours

lState	ShiftID	TZStart	TZEnd
24	1	28/04/2014 15:31	28/04/2014 18:00
24	2	28/04/2014 09:00	29/04/2014 18:00
24	3	29/04/2014 09:00	29/04/2014 08:43

Re: Re: Splitting the data in Work Hours & Days

Anonymous — Thu, 01 May 2014 18:09:00 GMT

See if this is close enough to what you need.

In the script, I added field Date:

LEFT JOIN (RESULT) LOAD DISTINCT

lState,

date(floor(TZStart)) as Date

RESIDENT RESULT;

The rest of the logic is in the expressions on the front end.

Re: Re: Splitting the data in Work Hours & Days

Fri, 02 May 2014 11:27:34 GMT

Yes. I really appreciated.

I noticed that you based your solution (which really help me) on "TZDates" (TZStart & TZEnd). Is it possible do to the same based on tStart and tEnd?

Best Regards,

Fernando

Re: Splitting the data in Work Hours & Days

Anonymous — Fri, 02 May 2014 11:57:12 GMT

Hmm, my chart expressions are using tStart and tEnd... I used TZ only in the script for the sake of simplicity.

Re: Splitting the data in Work Hours & Days

Fri, 02 May 2014 12:18:54 GMT

Tks anyway!