11 Replies Latest reply: Aug 25, 2016 10:19 AM by Henrique Demarco RSS

    Dynamic SUMIFS in set analysis

    Henrique Demarco

      Hi all,

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

       

      Type_apDATE_TIME_STARTDATE_TIME_ENDTIME_ELAPSEDNET_TIME_ELAPSED
      Production06/07/2016 05:2006/07/2016 10:575,63333335,633333334
      Production06/07/2016 10:5806/07/2016 12:582,01666672,016666667
      Production06/07/2016 12:5906/07/2016 13:390,68333330,683333333
      Production06/07/2016 13:4006/07/2016 17:003,353,35
      Production06/07/2016 17:0106/07/2016 21:594,98333332,983333334
      Stop06/07/2016 17:0106/07/2016 17:480,8
      Stop06/07/2016 18:0006/07/2016 18:200,35
      Stop06/07/2016 20:0006/07/2016 20:500,85
      Production06/07/2016 22:0007/07/2016 05:197,33333336,61666666
      Stop07/07/2016 01:0007/07/2016 01:420,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

        • Re: Dynamic SUMIFS in set analysis
          Gysbert Wassenaar
          Production 06/07/2016 17:01 06/07/2016 21:59 4,9833333 4,983333334
          Stop 06/07/2016 17:01 06/07/2016 17:48 0,8

          How can one machine do both Production and Stop during the same time?

            • Re: Dynamic SUMIFS in set analysis
              Henrique Demarco

              I agree with you.

              But considering inputs are manuals, I will not change the logic of a whole factory if I can solve it in a simple way. Otherwise factory must hire more people just to be inputting every stop and restart.

                • Re: Dynamic SUMIFS in set analysis
                  Gysbert Wassenaar

                  That's fine, but I don't understand the logic. How is determined what should be subtracted from what? Unless you can give an exact algorithm for that I don't know how to create a solution.

                    • Re: Dynamic SUMIFS in set analysis
                      Henrique Demarco

                      I need to subtract all stopped time, that are inside a production interval.

                      In example, my production shift is from 9am to 5pm. This interval contains some stops that I also know begin and end. First stop from 10am to 11am due to machine maintenance. Then I have another stop from noon to 1pm for operator lunch. So my net production elapsed time is not 8 hours, but 6.

                        • Re: Dynamic SUMIFS in set analysis
                          Gysbert Wassenaar

                          Perhaps something like this:

                           

                          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'

                            ;

                           

                          Temp1:

                          LOAD

                            DATE_TIME_START ,

                            DATE_TIME_END ,

                            TIME_ELAPSED - Stop_TIME_ELAPSED as NET_TIME_ELAPSED

                          RESIDENT

                            Data

                          WHERE

                              Stop_DATE_TIME_START >= DATE_TIME_START

                            AND Stop_DATE_TIME_END <= DATE_TIME_END

                            ;

                           

                          Temp2:

                          LOAD

                              DATE_TIME_START ,

                            DATE_TIME_END ,

                            TIME_ELAPSED  

                          FROM

                            source_table

                          WHERE

                            Type_ap = 'Production'

                            ;

                           

                          JOIN (Temp2)

                           

                          LOAD

                              DATE_TIME_START ,

                            DATE_TIME_END ,

                            TIME_ELAPSED    , 

                            NET_TIME_ELAPSED

                          RESIDENT

                              Temp1

                            ;

                           

                          Result:

                          LOAD

                              DATE_TIME_START ,

                            DATE_TIME_END ,

                            TIME_ELAPSED    , 

                            sum(NET_TIME_ELAPSED) as NET_TIME_ELAPSED

                          RESIDENT

                              Temp2

                          GROUP BY

                            DATE_TIME_START ,

                            DATE_TIME_END ,

                            TIME_ELAPSED   

                            ;

                           

                          DROP TABLES Data, Temp1, Temp2;

                            • Re: Dynamic SUMIFS in set analysis
                              Henrique 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'

                    • Re: Dynamic SUMIFS in set analysis
                      Alluraiah Allu

                      Hi ,

                       

                      try this , But you have look in to your Date formats ..

                       

                      SUM( {<Type_ap={"stop"},DATE_TIME_START={">=$(=max(DATE_TIME_START))"},

                                  DATE_TIME_END={"<=$(=max(DATE_TIME_END))">}       TIME_ELAPSED)

                      • Re: Dynamic SUMIFS in set analysis
                        Henrique 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);