input sum of value from another table

[#1]

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

(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');

[swuehl]

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;

[#3]

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).

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...

http://www.hist-europe.fr/liens/RS_Oxybul_Indicateurs.qvw

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



(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



(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;

[#4 · Accepted solution]

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



(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



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





Evolution:

join (Mantis) LOAD id,

1 as Compteur_Evolution,

     charge,

     date_livraison,

     description



FROM



(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;