Dynamic SUMIFS in set analysis

[h_demarco]

Hi all,

I have a table that contains production and stops data of a machine:

Type_ap	DATE_TIME_START	DATE_TIME_END	TIME_ELAPSED	NET_TIME_ELAPSED
Production	06/07/2016 05:20	06/07/2016 10:57	5,6333333	5,633333334
Production	06/07/2016 10:58	06/07/2016 12:58	2,0166667	2,016666667
Production	06/07/2016 12:59	06/07/2016 13:39	0,6833333	0,683333333
Production	06/07/2016 13:40	06/07/2016 17:00	3,35	3,35
Production	06/07/2016 17:01	06/07/2016 21:59	4,9833333	2,983333334
Stop	06/07/2016 17:01	06/07/2016 17:48	0,8
Stop	06/07/2016 18:00	06/07/2016 18:20	0,35
Stop	06/07/2016 20:00	06/07/2016 20:50	0,85
Production	06/07/2016 22:00	07/07/2016 05:19	7,3333333	6,61666666
Stop	07/07/2016 01:00	07/07/2016 01:42	0,7166667

In Excel I am calculating using SUMIFS (example regarding first row)

=D2-SUMIFS($D$2:$D$11;$A$2:$A$11;"Stop";$B$2:$B$11;">="&B2;$C$2:$C$11;"<="&C2)

Which means that for each row I am subtracting STOP TIME_ELAPSED from PRODUCTION TIME_ELAPSED, but only if STOP START TIME is greater or equal than PRODUCTION START TIME and if STOP END TIME is lower or equal than PRODUCTION END TIME.

Stop time must be between production time to be subtracted and generated NET_TIME_ELAPSED.

Any suggestion on how to create NET_TIME_ELAPSED in Qlikview using Set Analysis?

Thanks

NET_TIME_ELAPSED was wrong. Instead of 4,9833333 should be 2,983333334 and instead of 7,3333333 should be 6,61666666

[h_demarco]

We are getting closer. Thanks for your help.

During first join I get Out-of-Memory error.

Data:

LOAD

  DATE_TIME_START ,

  DATE_TIME_END ,

  TIME_ELAPSED

FROM

  source_table

WHERE

  Type_ap = 'Production'

  ;

JOIN (DATA)

LOAD

  DATE_TIME_START as Stop_DATE_TIME_START,

  DATE_TIME_END as Stop_DATE_TIME_END,

  TIME_ELAPSED as Stop_TIME_ELAPSED

FROM

  source_table

WHERE

  Type_ap = 'Stop'

[h_demarco]

Back to beginning, now data is more structured:

Here is table for PRODUCTION data load:

Apontamentos:
LOAD 'Produção' As Tipo_ap,
     RECURSO, 
     DATA_HORA_INI, 
     DATA_HORA_FIM, 
     Num((DATA_HORA_FIM+Time('00:01:00')-DATA_HORA_INI)*24,'#######') As Duração,
FROM QVDs\VIEW_PCPAPPRODUCAO.qvd (qvd);

And now I need to LEFT JOIN table STOP:

LEFT JOIN (Apontamentos) 
LOAD Sum(Duração_parada)
Resident Parada
Where DATA_HORA_INI<=DATA_HORA_INI_Parada AND DATA_HORA_FIM_Parada <=DATA_HORA_FIM;

But appears error:

Field not found - <DATA_HORA_INI>
LEFT JOIN 
LOAD Sum(Duração_parada)
Resident Parada
Where DATA_HORA_INI<=DATA_HORA_INI_Parada AND DATA_HORA_FIM_Parada <=DATA_HORA_FIM

My intention is to add a column [Duração_parada] for each row in [Apontamentos], only when DATA_HORA_INI<=DATA_HORA_INI_Parada AND DATA_HORA_FIM_Parada <=DATA_HORA_FIM

Any idea on how to deal with error?

Just in case Parada table is:

Parada:
LOAD 'Parada' As Tipo_ap,
  DATA_HORA_INI As DATA_HORA_INI_Parada, 
     DATA_HORA_FIM As DATA_HORA_FIM_Parada, 
     Num((DATA_HORA_FIM+Time('00:01:00')-DATA_HORA_INI)*24,'#######') As Duração_parada
FROM QVDs\VIEW_PCPAPPARADA.qvd (qvd);