Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

cuniberto
New Contributor III

sum without overlapping intervals in load script

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 1interval 113/03/201810.1311.151.02
ENG 1interval 213/03/201811.3012.301.00
ENG 1interval 313/03/201812.0012.300.30

TOTAL2.02

interval 3 is overlapped.

1 Solution

Accepted Solutions
Highlighted
cuniberto
New Contributor III

Re: sum without overlapping intervals in load script

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;

View solution in original post

4 Replies

Re: sum without overlapping intervals in load script

So you want to calculate this sum in the script?

MVP & Luminary
MVP & Luminary

Re: sum without overlapping intervals in load script

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

cuniberto
New Contributor III

Re: sum without overlapping intervals in load script

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

Cattura3.JPG

Highlighted
cuniberto
New Contributor III

Re: sum without overlapping intervals in load script

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;

View solution in original post