topic Re: sum without overlapping intervals in load script in QlikView

sum without overlapping intervals in load script

cuniberto — Wed, 25 Nov 2020 16:16:04 GMT

Ciao,

i would like to sum the intervals of machine work only when they are not overlapped.

this is because if the machine work on two production order i don't want to duplicate worked time.

i hope the following table would explain better than my english.

ENG 1	interval 1	13/03/2018	10.13	11.15	1.02
ENG 1	interval 2	13/03/2018	11.30	12.30	1.00
ENG 1	interval 3	13/03/2018	12.00	12.30	0.30

TOTAL

2.02

interval 3 is overlapped.

Re: sum without overlapping intervals in load script

sunny_talwar — Wed, 14 Mar 2018 11:15:42 GMT

So you want to calculate this sum in the script?

Re: sum without overlapping intervals in load script

marcus_sommer — Wed, 14 Mar 2018 11:38:41 GMT

You could do it with the help of Peek() or Previous() ? maybe with a logic like this:

...

if(Key = previous(Key) and Date = previous(Date) and Start < previous(End), 1, 0) as Flag

...

You might need some more if-conditions to fetch all possible combinations but then using this flag as a set analysis condition in the UI you could sum the values. Whereby also a cummulating within the script will be possible.

- Marcus

Re: sum without overlapping intervals in load script

cuniberto — Thu, 15 Mar 2018 14:36:39 GMT

i would like to obtain a sum like the one represented in attechment: when the interval is overlapped i would have the difference between the max end hour and the min value

Re: sum without overlapping intervals in load script

cuniberto — Mon, 19 Mar 2018 17:02:17 GMT

i've solved in this way:

temp:

load

if(begin<peek(end),peek(ID),rowno()) as ID

residen time_table;

in this way if the begin is overlapped to previous end it takes same id, then i used

time_differences:

load

max(end)-min(begin) as time_difference

resident temp group by ID;

Re: sum without overlapping intervals in load script

cata_pli — Thu, 14 Oct 2021 13:53:27 GMT

It's an old thread, but maybe someone will see it.

I think in temp table you need to add one more condition to cover the case where you have at least 3 simultaneous intervals like this:

			begin	end
ENG 1	interval 1	13/03/2018	10	11	1
ENG 1	interval 2	13/03/2018	10.1	10.3	0.2
ENG 1	interval 3	13/03/2018	10.6	12	1.4
		TOTAL			2

Your condition: if(begin<peek(end),peek(ID), rowno()) as ID ... applied for interval 3 will not work ok (it will give false) and it will duplicate interval 3, although interval 1 is still running. It will compare 10.6 with 10.3, but it should compare 10.6 with 11.

This will be resolved if we also put the next condition in temp:

if( end < peek(end), peek(end), end) as end

That way it will replace end of interval 2 (10.3) with end of interval 1 (11) and it will not consider interval 3 having a new ID.