Hi,
i've two table
VeryBigTable:
load * inline [
DateTime,line,KrateTime
'2011-01-28','001','21:20:01'
'2011-01-28','001','21:20:03'
'2011-01-28','001','21:22:01'
'2011-01-28','001','21:23:01'
'2011-01-28','001','21:24:01'
'2011-01-28','001','21:25:01'
'2011-01-28','001','21:26:01'
'2011-01-28','001','21:27:03'
'2011-01-28','001','21:28:01'
'2011-01-28','001','21:29:01'
'2011-01-28','001','21:30:01'
'2011-01-28','001','21:31:01'
'2011-01-28','001','21:40:01'
'2011-01-28','001','21:51:01'
'2011-01-28','002','21:20:03'
'2011-01-28','002','21:30:01'
'2011-01-28','002','21:40:01'
'2011-01-28','002','21:50:01'
'2011-01-28','002','21:60:01'
];
Pause:
load * inline [
line,PauseStart,PauseStop
'001','21:25','21:27'
'001','21:30','21:35'
'001','08:55','08:59'
'002','21:25','21:30'
];
I want to calcute the total time of a line is busy, i do that with a pivot table with min(KratTime) and max(KratTime), that's no problem. But i cannot calculate the correct pause time, because not every pause is taken.
With sum(interval(PauseStop - PauseStart) all the pause are calculated, but that is not correct.
using something like this sum(interval(if(min(KratTime) < PauseStop and max(KratTime) > PauseStart,PauseStop - PauseStart,0)), will not work because Qlikview will not let me use de min en max function, replacing is with colomn(x) wil also not work, but the expression is OK, so qlikview says.
Solving the problem in the load file, will take a lot of memory (very much because the table is big, million of record) with joins.
any idea's
thanks
Frank
