topic Re: Task/Ticket Duration in QlikView

Task/Ticket Duration

Anonymous — Tue, 10 Nov 2015 10:50:43 GMT

I am facing the challenge to calculate the duration of Tasks/tickets Duration and exclude times outside working hours and exclude breaks.

Currently working hours and breaks are defined in a table (to make it more flexible). In script I run through all data (using peek) and calculate the duration by subtracting breaks. Unfortunatley this is very slow (e.g. 20000 rows need about 3 minutes).

Is there a Chance to calculate this on surface?

e.g. Working hour 8:00 - 17:00

Break1: 9:00-9:15

Break2: 12:00-12:30

Break3: 14:00-14.45

Taskevent might be

08:30-13:00 - which should result in 3h 45 Duration

09:10-09:30 - which should result in 15 minutes

10:00-10:10 - which should result in 10 minutes

Task can occur at any time and be ended at any time

I prefer a solution with is able to handle flexible times (working hours, breaks, no of breaks etc.) so I can use it for other Projects as well

Any idea? Is

Re: Task/Ticket Duration

Gysbert_Wassenaar — Tue, 10 Nov 2015 11:00:38 GMT

Is there a Chance to calculate this on surface?

Do you mean in chart expressions? That will make the user experience the performance penalty. And performance will likely be even worse than calculating it in the script.

Perhaps it's an idea to implement incremental loading if you haven't already. Then you'll only have to process the new records and not all of them again and again.

Re: Task/Ticket Duration

marcus_sommer — Tue, 10 Nov 2015 11:03:43 GMT

About 3 minutes for 20000 rows seems to be quite long even with peek() functions - could you share the script?

- Marcus

Re: Task/Ticket Duration

Anonymous — Tue, 10 Nov 2015 11:22:15 GMT

i can share the script later on

meanwhile I think about an optimization

right now I get each datarow (using peek) and

then in an inner Loop I go through all breaks (using peek)

maybe I can get rid of the inner loop?

I will provide the script part this evening

Re: Task/Ticket Duration

Anonymous — Tue, 10 Nov 2015 11:25:24 GMT

thats what I was afraid of, that is not faster on surface.

I need to check if incremental load is possible, rigth now i get full load and data only for 3 months
(as this is a new Project). But size will increase in future. So incremental load should really be considered!

Re: Task/Ticket Duration

Gysbert_Wassenaar — Tue, 10 Nov 2015 11:27:34 GMT

I'm pretty sure you can get rid of the inner loop. Perhaps this discussion helps: Calculate hours between two Date/Time strings

Re: Task/Ticket Duration

swuehl — Tue, 10 Nov 2015 11:28:28 GMT

Looping over each record using something like FOR ... NEXT will indeed take ages.

You can use WHILE clauses in your load, like this:

Set DecimalSep ='.';

Let vWorkStart = Num('08:00');

Let vWorkEnd = Num('17:00');

Let vBreak1Start = Num('09:00');

Let vBreak1End = Num('09:15');

Let vBreak2Start = Num('12:00');

Let vBreak2End = Num('12:30');

Let vBreak3Start = Num('14:00');

Let vBreak3End = Num('14:45');

TASKS:

LOAD Recno() as TaskID, * INLINE [

Start, End

08:30,13:00

09:10,09:30

10:00,10:10

07:00, 19:00

];

TMP:

LOAD *, interval(End-Start ) as Duration;

LOAD TaskID, pick(iterno(), RangeMin(Start,$(vBreak3Start)), RangeMax($(vBreak3End),Start)) as Start, pick(iterno(), RangeMin(End,$(vBreak3Start)), RangeMax(End,$(vBreak3End))) as End

WHILE iterno() <=2;

LOAD TaskID, pick(iterno(), RangeMin(Start,$(vBreak2Start)), RangeMax($(vBreak2End),Start)) as Start, pick(iterno(), RangeMin(End,$(vBreak2Start)), RangeMax(End,$(vBreak2End))) as End

WHILE iterno() <=2;

LOAD TaskID, pick(iterno(), RangeMin(Start,$(vBreak1Start)), RangeMax($(vBreak1End),Start)) as Start, pick(iterno(), RangeMin(End,$(vBreak1Start)), RangeMax(End,$(vBreak1End))) as End

WHILE iterno() <=2;

LOAD TaskID, RangeMax($(vWorkStart), Start) as Start, RangeMin($(vWorkEnd), End) as End RESIDENT TASKS;

DROP TABLE TASKS;

RESULT:

LOAD TaskID, Interval(Sum(Duration)) as DurationTOTAL

Resident TMP

GROUP BY TaskID;

Re: Task/Ticket Duration

swuehl — Tue, 10 Nov 2015 11:39:40 GMT

Noticed that the while loops generated unnecessary amount of records, here is a slightly improved version:


Set DecimalSep ='.';


Let vWorkStart = Num('08:00');
Let vWorkEnd = Num('17:00');
Let vBreak1Start = Num('09:00');
Let vBreak1End =  Num('09:15');
Let vBreak2Start = Num('12:00');
Let vBreak2End =  Num('12:30');
Let vBreak3Start = Num('14:00');
Let vBreak3End =  Num('14:45');


TASKS:
LOAD Recno() as TaskID, * INLINE [
Start, End
08:30,13:00    
09:10,09:30   
10:00,10:10
07:00, 19:00  
];

TMP:
LOAD *, interval(End-Start ) as Duration;


LOAD TaskID, 
  pick(iterno(), RangeMin(Start,$(vBreak3Start)), RangeMax($(vBreak3End),Start)) as Start, 
  pick(iterno(), RangeMin(End,$(vBreak3Start)), RangeMax(End,$(vBreak3End))) as End
WHILE iterno() <= (1 -  (End > $(vBreak2End))); 

LOAD TaskID, 
  pick(iterno(), RangeMin(Start,$(vBreak2Start)), RangeMax($(vBreak2End),Start)) as Start, 
  pick(iterno(), RangeMin(End,$(vBreak2Start)), RangeMax(End,$(vBreak2End))) as End
WHILE iterno() <= (1  -( End > $(vBreak1End))); 


LOAD TaskID, 
  pick(iterno(), RangeMin(Start,$(vBreak1Start)), RangeMax($(vBreak1End),Start)) as Start, 
  pick(iterno(), RangeMin(End,$(vBreak1Start)), RangeMax(End,$(vBreak1End))) as End
WHILE iterno() <=2; 


LOAD TaskID, 
  RangeMax($(vWorkStart), Start) as Start, 
  RangeMin($(vWorkEnd), End) as End 
RESIDENT TASKS; 


DROP TABLE TASKS;


RESULT:
LOAD TaskID, Interval(Sum(Duration)) as DurationTOTAL
Resident TMP
GROUP BY TaskID;

Re: Task/Ticket Duration

Anonymous — Tue, 10 Nov 2015 12:01:22 GMT

wow, great

I will try your suggestion asap

that's why I like this community. I get fast answers and good advices

I will give feedback after tests

thanks a lot

Re: Task/Ticket Duration

swuehl — Tue, 10 Nov 2015 12:10:23 GMT

I think you can even limit the first WHILE clause, too.

TMP:

LOAD *, interval(End-Start ) as Duration;

LOAD TaskID,

pick(iterno(), RangeMin(Start,$(vBreak3Start)), RangeMax($(vBreak3End),Start)) as Start,

pick(iterno(), RangeMin(End,$(vBreak3Start)), RangeMax(End,$(vBreak3End))) as End

WHILE iterno() <= (1 - (End > $(vBreak2End)));

LOAD TaskID,

pick(iterno(), RangeMin(Start,$(vBreak2Start)), RangeMax($(vBreak2End),Start)) as Start,

pick(iterno(), RangeMin(End,$(vBreak2Start)), RangeMax(End,$(vBreak2End))) as End

WHILE iterno() <= (1 -( End > $(vBreak1End)));

LOAD TaskID,

pick(iterno(), RangeMin(Start,$(vBreak1Start)), RangeMax($(vBreak1End),Start)) as Start,

pick(iterno(), RangeMin(End,$(vBreak1Start)), RangeMax(End,$(vBreak1End))) as End

WHILE iterno() <= (1 -( End > $(vBreak1Start)));

LOAD TaskID,

RangeMax($(vWorkStart), Start) as Start,

RangeMin($(vWorkEnd), End) as End

RESIDENT TASKS;

Re: Task/Ticket Duration

Anonymous — Tue, 10 Nov 2015 22:34:51 GMT

I tested your script and tried to adapt it to my data

your solution is very fast!!

the data is not yet correct, but that is due to the Adaption of my data

I Need to continue on thursday, so meanwhile thank for your solution

If I get your solution working with my data, I give you a feedback

and I may document the final solution (I have additional calculations to do)