Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
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