3 Replies Latest reply: Jul 8, 2012 11:39 AM by stephane jeanneteau RSS

    input sum of value from another table

      hello,

       

      I need some help for one request.

       

      I want to fill the field durée_travail with the sum of the value of the field temps of a another table (origine1) which has several lines with the same id but with different statut.

       

      Table origine 1

      N_mantis (id),statut,temps

      11,open,2

      11,work in progress,4

      11, closed,6

      17,open,5

      17,pending customer,8

      17, closed,6

       

      Table origine 2

      N_mantis (id),temps

      11, 6

      17,5

       

      The field temps is the sum of only the statuts open and work in progress of the lines which have the same id (N_mantis) of the Table origine 1

       

      Origine1

      Load

            N_mantis

            statut,

             temps

      FROM
      [C:\Users\SJEANNETEAU\Desktop\test tables rs\oxybul\Oxybul - indicateurs.xlsx]
      (
      ooxml, embedded labels, table is Evolutions);

       


      origine2

      LOAD

            N_mantis ,
           
      categorie as Cat,
           
      compteur as comp,
          
      version as ver,
          
      severite as sev,     
          
      date_changement as date1 ,
           
      Prioritaire as prio,
          
      [Etat en cours] as etet,
          
      Date_fin as date2,
          
      Statut as stat,
          
      durée_travail

      RESIDENT origine
      where N_mantis <> peek('N_mantis');

        • Re: input sum of value from another table
          Stefan Wühl

          I believe you can do this with a group by load of table 1 and a join:

           

          First load your tables then

           

          LEFT JOIN (origine2) LOAD

          N_mantis,

          sum(temps) as durée_travail

          resident origine1

          where statut = 'open' or statut = 'work in progress' group by N_mantis;

           

           

            • Re: input sum of value from another table
              Thanks swuehl,
              But this method generates too many lines...
              the first table (origine) is all data about the incidents,with one line per statut for each id (N_mantis)
              The second table (mantis) is the synthesis of the incident, so i need only one line per id, with the total of duration time (durée_travail).
              With your method, the second table has got now several lines per ID, with one line with all data (this one is good) and the other lines have blank data (theses one i don't need).
              ScreenHunter_93 Jul. 08 10.38.gif
              I wanted to calculate the sum of three fields with only two statuts

              sum

              (Tempsfin) + sum(tempsdebut) + sum(jourtravail) as durée_travail
              where Statut = 'Affecté' or Statut = 'Nouveau' group by N_mantis;

               

              the sum is correct, but i have only one statut, not the sum of the two statuts...
              I also  would like to fill some fields of mantis table with data from origine table :
              - statut_courant of mantis table is the value of Staut field of origine table which has the highest date_changement for the same N_mantis
              - Date_début_mantis of mantis table is the value of date_changement field of origine table which has the value "Nouveau" in the field  Statut of origine table for the same N_mantis
              - Date_fin_mantis of mantis table is the value of date_changement field of origine table which has the value "Fermé" in the field  Statut of origine table for the same N_mantis
              this is the actaul script :
              SET
              ThousandSep=' ';

              SET DecimalSep=',';

              SET MoneyThousandSep=' ';

              SET MoneyDecimalSep=',';

              SET MoneyFormat='# ##0,00 €;-# ##0,00 €';

              SET TimeFormat='hh:mm:ss';

              SET DateFormat='DD/MM/YYYY';

              SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

              SET MonthNames='janv.;févr.;mars;avr.;mai;juin;juil.;août;sept.;oct.;nov.;déc.';

              SET DayNames='lun.;mar.;mer.;jeu.;ven.;sam.;dim.';





              //Let vStartTime = num(maketime(8,30)); //8:30

              //Let vCloseTime = num(maketime(18,30)); // 18:30

              //Let vFullDay = num(maketime(10)); // 10 hours - full day



              HolidayTable:

              LOAD chr(39) & concat(distinct Date,chr(39)&','&chr(39) ) & chr(39) as HolidayList;

              LOAD * INLINE [

              Date

              '01/01/2010'    

              '01/01/2011'    

              '01/01/2012'    

              '01/01/2013'    

              '01/01/2014'

              '05/04/2010'    

              '25/04/2011'    

              '09/04/2012'    

              '01/04/2013'    

              '21/04/2014'

              '01/05/2010'    

              '01/05/2011'    

              '01/05/2012'    

              '01/05/2013'    

              '01/05/2014'

              '08/05/2010'    

              '08/05/2011'    

              '08/05/2012'    

              '08/05/2013'    

              '08/05/2014'

              '24/05/2010'    

              '02/06/2011'    

              '17/05/2012'    

              '09/05/2013'    

              '29/05/2014'

              '13/05/2010'    

              '13/06/2011'    

              '28/05/2012'    

              '20/05/2013'    

              '09/06/2014'

              '14/07/2010'    

              '14/07/2011'    

              '14/07/2012'    

              '14/07/2013'    

              '14/07/2014'

              '15/08/2010'    

              '15/08/2011'    

              '15/08/2012'    

              '15/08/2013'    

              '15/08/2014'

              '01/11/2010'    

              '01/11/2011'    

              '01/11/2012'    

              '01/11/2013'    

              '01/11/2014'

              '11/11/2010'    

              '11/11/2011'    

              '11/11/2012'    

              '11/11/2013'    

              '11/11/2014'

              '25/12/2010'    

              '25/12/2011'    

              '25/12/2012'    

              '25/12/2013'    

              '25/12/2014'



              ]
              ;



              Let vHolidayList = peek('HolidayList',0,'HolidayTable');



              origine:

              LOAD *, (Tempsfin + tempsdebut + jourtravail) as durée_travail ;

              LOAD recno() as "Record ID",

               
              categorie,

                  
              id as N_mantis,

                  
              version,

                  
              severite,     

                  
              date_changement ,

                  
              //calcul des heures travaillées sur le jour début

                   if ( NetWorkDays(date_changement,date_changement,$(vHolidayList) ) >=1, if ( Hour (date_changement ) + ((Minute (date_changement )/60))<8.5,1,

                  
              if ( Hour (date_changement ) + ((Minute (date_changement )/60))>= 18.5,0, ((18.5 - (Hour (date_changement ) + ((Minute (date_changement )/60))))/10)) ),0) as tempsdebut,

                  
              // mise en forme de la date début selon les périodes

                   date(monthstart(date_changement), 'MMM-YYYY') AS MoisAnnée,

               
              date(Daystart(date_changement), 'DD-MMM-YYYY') as AnnéeMoisJour,

               
              dual(week(date_changement)&'-'&WeekYear(date_changement), WeekStart(date_changement)) as SemaineAnnée,

               
              // modification des libellés du champ prioritaire 

                   if(Prioritaire='Basse', dual('1-Basse', 1),

                 
              if(Prioritaire ='Normale', dual('2-Normale', 2),

                    
              if(Prioritaire ='Elevée', dual('3-Elevée', 3)

                       )

                    )

                 )
              as Prioritaire,

                  
              [Etat en cours],

                  
              Date_fin,

                  
              // mise en forme de la date début selon les périodes

                   date(monthstart(Date_fin), 'MMM-YYYY') AS MoisAnnéedatefin,

               
              date(Daystart(Date_fin), 'DD-MMM-YYYY') as AnnéeMoisJourdatefin,

               
              dual(week(Date_fin)&'-'&WeekYear(Date_fin), WeekStart(Date_fin)) as SemaineAnnéedatefin,

                  
              //calcul des heures travaillées sur le jour fin

                    if (NetWorkDays(Date_fin,Date_fin,$(vHolidayList) ) >=1, if ( Hour (Date_fin ) + ((Minute (Date_fin )/60))<8.5,0,

                 
              if (Hour (Date_fin ) + ((Minute (Date_fin )/60))>= 18.5,1, ((((Hour (Date_fin ) + (Minute (Date_fin )/60))-8.5)) /10))),0) as Tempsfin,

                   
              //calcul des jours travaillés entre la date début et la date fin

                    NetWorkDays(date_changement,Date_fin,$(vHolidayList)) as jourtravail,

                  
              Statut



              FROM

              [C:\Users\SJEANNETEAU\Desktop\test tables rs\oxybul\Oxybul - indicateurs.xlsx]

              (
              ooxml, embedded labels, table is Données);





              Rvolution:

              LOAD id as N_mantis2,

              1
              as Compteur_Evolution,

                  
              charge,

                  
              date_livraison,

                  
              // mise en forme de la date livraison selon les périodes

                   date(monthstart(date_livraison), 'MMM-YYYY') AS MoisAnnée_livraison,

               
              date(Daystart(date_livraison), 'DD-MMM-YYYY') as AnnéeMoisJour_livraison,

               
              dual(week(date_livraison)&'-'&WeekYear(date_livraison), WeekStart(date_livraison)) as SemaineAnnée_livraison,

                  
              description



              FROM

              [C:\Users\SJEANNETEAU\Desktop\test tables rs\oxybul\Oxybul - indicateurs.xlsx]

              (
              ooxml, embedded labels, table is Evolutions);









              Mantis:

              LOAD N_mantis ,

               
              categorie as Catégorie,

                  
              severite as sévérité,     

                  
              date_changement as date_début_mantis,

                  
              // mise en forme de la date livraison selon les périodes

                   date(monthstart(date_changement), 'MMM-YYYY') AS MoisAnnéedate_début_mantis,

               
              date(Daystart(date_changement), 'DD-MMM-YYYY') as AnnéeMoisJourdate_début_mantis,

               
              dual(week(date_changement)&'-'&WeekYear(date_changement), WeekStart(date_changement)) as SemaineAnnéedate_début_mantis

                  
              Prioritaire as priorité,

                  
              [Etat en cours] as Etat,

                  
              Date_fin as date_fin_mantis,

                  
              Statut as Statut_courant,

               
              durée_travail

              RESIDENT origine

              where N_mantis <> peek('N_mantis');

              //drop table origine;



              LEFT JOIN (Mantis) LOAD

              N_mantis,

              sum(Tempsfin) + sum(tempsdebut) + sum(jourtravail) as durée_travail

              resident origine



              where Statut = 'Affecté' or Statut = 'Nouveau' group by N_mantis;
                • Re: input sum of value from another table

                  I've found it ! yes

                   

                  Statuts:

                   

                   

                  LOAD *, (Tempsfin + tempsdebut + jourtravail) as durée_travail ;

                  LOAD recno() as "Code",

                      
                  id,    

                      
                  date_changement as Date_début ,

                      
                  //calcul des heures travaillées sur le jour début

                       if ( NetWorkDays(date_changement,date_changement,$(vHolidayList) ) >=1, if ( Hour (date_changement ) + ((Minute (date_changement )/60))<8.5,1,

                      
                  if ( Hour (date_changement ) + ((Minute (date_changement )/60))>= 18.5,0, ((18.5 - (Hour (date_changement ) + ((Minute (date_changement )/60))))/10)) ),0) as tempsdebut,

                      
                  // mise en forme de la date début selon les périodes

                       date(monthstart(date_changement), 'MMM-YYYY') AS MoisAnnéeDate_début,

                   
                  date(Daystart(date_changement), 'DD-MMM-YYYY') as AnnéeMoisJourDate_début,

                   
                  dual(week(date_changement)&'-'&WeekYear(date_changement), WeekStart(date_changement)) as SemaineAnnéeDate_début,

                   
                  Year (date_changement) as AnnéeDate_début,   

                      
                  Date_fin,

                      
                  // mise en forme de la date début selon les périodes

                       date(monthstart(Date_fin), 'MMM-YYYY') AS MoisAnnéedatefin,

                   
                  date(Daystart(Date_fin), 'DD-MMM-YYYY') as AnnéeMoisJourdatefin,

                   
                  dual(week(Date_fin)&'-'&WeekYear(Date_fin), WeekStart(Date_fin)) as SemaineAnnéedatefin,

                   
                  Year (Date_fin) as AnnéeDate_fin,

                      
                  //calcul des heures travaillées sur le jour fin

                        if (NetWorkDays(Date_fin,Date_fin,$(vHolidayList) ) >=1, if ( Hour (Date_fin ) + ((Minute (Date_fin )/60))<8.5,0,

                     
                  if (Hour (Date_fin ) + ((Minute (Date_fin )/60))>= 18.5,1, ((((Hour (Date_fin ) + (Minute (Date_fin )/60))-8.5)) /10))),0) as Tempsfin,

                       
                  //calcul des jours travaillés entre la date début et la date fin

                        NetWorkDays(date_changement,Date_fin,$(vHolidayList)) as jourtravail,

                      
                  Statut



                  FROM

                  [C:\Users\SJEANNETEAU\Desktop\test tables rs\oxybul\Oxybul - indicateurs.xlsx]

                  (
                  ooxml, embedded labels, table is Données);



                  Mantis:

                  LOAD

                   
                  distinct id,

                   
                  categorie as Catégorie,

                      
                  severite as sévérité,

                      
                  version,

                   
                  // modification des libellés du champ prioritaire 

                       if(Prioritaire='Basse', dual('1-Basse', 1),

                     
                  if(Prioritaire ='Normale', dual('2-Normale', 2),

                        
                  if(Prioritaire ='Elevée', dual('3-Elevée', 3)

                           )

                        )

                     )
                  as Prioritaire,

                      
                  [Etat en cours]





                  FROM

                  [C:\Users\SJEANNETEAU\Desktop\test tables rs\oxybul\Oxybul - indicateurs.xlsx]

                  (
                  ooxml, embedded labels, table is Données);





                  Evolution:

                  join (Mantis) LOAD id,

                  1
                  as Compteur_Evolution,

                      
                  charge,

                      
                  date_livraison,

                      
                  description



                  FROM

                  [C:\Users\SJEANNETEAU\Desktop\test tables rs\oxybul\Oxybul - indicateurs.xlsx]

                  (
                  ooxml, embedded labels, table is Evolutions);







                  LEFT JOIN (Mantis) LOAD

                  id,

                  sum(durée_travail) as durée_travail2

                  resident Statuts



                  where Statut = 'Affecté' or Statut = 'Nouveau'  group by id;