Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
cuniberto
Contributor III
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
Contributor III
Contributor III
Author

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
sunny_talwar

So you want to calculate this sum in the script?

marcus_sommer
MVP & Luminary
MVP & Luminary

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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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