topic Re: help with interval time in QlikView

help with interval time

martinqlik — Thu, 08 Jun 2017 18:10:49 GMT

Hello all,

In my application*, I calculate the execution time between several different activities. Actually, I am showing that in total time. However, I need to show that in working hours, Monday to Friday, and excluding holidays.

Does anyone knows how can I do that?

Thank y'all,

*qvw has been attached.

Re: help with interval time

johnw — Thu, 08 Jun 2017 21:42:29 GMT

A couple not-fully-formed ideas.

Load a list of all weekends and holidays. Autogenerate records for working hours for all days between the start and end timestamp. Load where not exists in the list of weekends and holidays. Group by with sum() to get the total.
Use networkingdays() with all holidays concatenated into a variable you can use, multiply by work hours per day, then subtract out the right amount for the start and end times.

Re: help with interval time

swuehl — Thu, 08 Jun 2017 21:52:34 GMT

Maybe have a look at

Calculate hours between two Date/Time strings

AFAIR, it demonstrates both ideas mentioned by John.

Re: help with interval time

martinqlik — Fri, 09 Jun 2017 13:03:49 GMT

Hello, John!

Start and End times are defined as 8:00 AM to 6:00 PM.

Thank you for answering.

Re: help with interval time

martinqlik — Fri, 09 Jun 2017 13:12:10 GMT

Hello, Stefan! How have you been?

I saw that post already, before I posted this issue. But, I my doubt still remaining. In this case you posted(Calculate hours between two Date/Time strings) isn't working on a case like this:

*Assuming that business hours starts at 8h00 AM to 6h00 PM

Activity Starts on 02/01/12 5h45 PM

Activity Ends on 02/01/12 8h15 AM

The interval of them, or execution time, must be 0:30 minutes. However, in the application it comes null. Seems like the application coverages only activities that has a Start and End in the same day.

Did you get my point?

Thank you for answering.

Re: help with interval time

swuehl — Fri, 09 Jun 2017 13:50:43 GMT

Not sure if I understand correctly, but in your example, end timestamp is before start timestamp, which doesn't make sense to me and will not work with the posted code for sure.

Re: help with interval time

martinqlik — Fri, 09 Jun 2017 14:05:41 GMT

Yes, you right, I meant this:

Activity Starts on 02/01/12 5h45 PM

Activity Ends on 03/01/12 8h15 AM

Total execution time: 0:30 minutes.

Thank you,

Re: help with interval time

swuehl — Fri, 09 Jun 2017 14:22:56 GMT

0:30 is what I get with both solutions (script based and expression based).

Double check that the timestamp format is correctly set and your values are correctly interpreted and stored as dual values.

If it's not working for you, then please post your current script.

Re: help with interval time

martinqlik — Fri, 09 Jun 2017 14:41:33 GMT

I have been using this script:

Set TimestampFormat = 'M/D/YY hh:mm TT';

Set vHol = '41130,41140';

INPUT:

LOAD *, recno() as ID INLINE [

DT1, DT2

8/18/12 09:20 AM, 8/20/12 01:13 PM

8/17/12 09:20 AM, 8/20/12 01:13 PM

8/17/12 09:20 AM, 8/19/12 01:13 PM

8/25/12 17:45 PM, 8/26/12 08:30 AM

6/27/12 12:41 PM, 7/6/12 4:38 PM

6/29/12 4:45 PM, 7/6/12 4:19 PM

8/1/12 09:00 AM, 8/3/12 10:00 AM

8/3/12 03:00 PM, 8/6/12 09:00 AM

];

TMP:

LOAD ID,

daystart(DT1)+iterno()-1 as Date,

if(iterno()=1, rangemin(rangemax(frac(DT1),maketime(8)),maketime(18)), maketime(8)) as Start,

if(daystart(DT1)+iterno()-1=daystart(DT2), rangemax(maketime(8),rangemin(frac(DT2),maketime(18))),Maketime(18)) as End

Resident INPUT

while daystart(DT2) >= daystart(DT1)+iterno()-1;

left join (INPUT)

LOAD

ID,

interval(sum(End-Start)) as Duration

Resident TMP where WeekDay(Date)<5 and not match(Date,$(vHol)) group by ID;

drop table TMP;

Re: help with interval time

swuehl — Fri, 09 Jun 2017 14:45:14 GMT

Just entering your sample timestamps, I do get 0:30:

Set TimestampFormat = 'M/D/YY hh:mm TT';

Set vHol = '41130,41140';

INPUT:

LOAD *, recno() as ID INLINE [

DT1, DT2

8/18/12 09:20 AM, 8/20/12 01:13 PM

8/17/12 09:20 AM, 8/20/12 01:13 PM

8/17/12 09:20 AM, 8/19/12 01:13 PM

8/25/12 17:45 PM, 8/26/12 08:30 AM

6/27/12 12:41 PM, 7/6/12 4:38 PM

6/29/12 4:45 PM, 7/6/12 4:19 PM

8/1/12 09:00 AM, 8/3/12 10:00 AM

8/3/12 03:00 PM, 8/6/12 09:00 AM

1/2/12 05:45 PM, 1/3/12 08:15 AM

];

TMP:

LOAD ID,

daystart(DT1)+iterno()-1 as Date,

if(iterno()=1, rangemin(rangemax(frac(DT1),maketime(8)),maketime(18)), maketime(8)) as Start,

if(daystart(DT1)+iterno()-1=daystart(DT2), rangemax(maketime(8),rangemin(frac(DT2),maketime(18))),Maketime(18)) as End

Resident INPUT

while daystart(DT2) >= daystart(DT1)+iterno()-1;

left join (INPUT)

LOAD

ID,

interval(sum(End-Start)) as Duration

Resident TMP where WeekDay(Date)<5 and not match(Date,$(vHol)) group by ID;

drop table TMP;

Re: help with interval time

martinqlik — Fri, 09 Jun 2017 17:33:44 GMT

Idk why, but this just work now!

Thank you!