Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Splitting the data in Work Hours & Days

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

1 Solution

Accepted Solutions
mov
Esteemed Contributor III

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

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.

14 Replies
mov
Esteemed Contributor III

Re: Splitting the data in Work Hours & Days

Fernando,

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

MVP
MVP

Re: Splitting the data in Work Hours & Days

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

Calculate hours between two Date/Time strings

Not applicable

Re: Splitting the data in Work Hours & Days

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

Not applicable

Re: Splitting the data in Work Hours & Days

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

mov
Esteemed Contributor III

Re: Splitting the data in Work Hours & Days

My example is about both time and weekend.

MVP
MVP

Re: Splitting the data in Work Hours & Days

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

Not applicable

Re: Splitting the data in Work Hours & Days

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.

Not applicable

Re: Splitting the data in Work Hours & Days

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:

lStateShiftIDTZStartTZEnd
24128/04/2014 15:3128/04/2014 18:00
24228/04/2014 09:0029/04/2014 18:00
24329/04/2014 09:0029/04/2014 08:43

Regards

Fernando

MVP
MVP

Re: Splitting the data in Work Hours & Days

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

Community Browser