Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
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
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

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