2 Replies Latest reply: Jul 7, 2012 3:25 PM by stephane jeanneteau RSS

    in load script, in can't sum differents field for creating another one

      Hello,

       

      I've got a problem with on field where I want to sum three fields... The systeme told me : >field didn't find- <Tempsfin>

       

      you have the script here, with the problem line in comment (green)

      can you tell me what is wrong?

       

      thank you

       

      Stephane

       

      origine:

       

       

      LOAD recno() as "Record ID",

      categorie,

          
      id as N_mantis,

           1
      as compteur,

          
      version,

          
      severite,     

          
      date_changement ,

          
      Year(date_changement ) as Année,

          
      Month(date_changement ) as Mois,

          
      Week (date_changement ) as Semaine,

          
      Day (date_changement ) as Jour,

          
      Hour (date_changement ) as theures,

          
      Minute (date_changement ) as minutes,

          
      Hour (date_changement )as heure,

          
      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,

          
      WeekDay (date_changement ) as JourSemaine,

          
      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

           
      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,

           
      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,

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

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

         

          
      Statut



      FROM

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

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

        • Re: in load script, in can't sum differents field for creating another one
          Nagaian Krishnamoorthy

          Correct your script as shown below:

           

          origine:

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

          LOAD recno() as "Record ID",

                    categorie,

               id as N_mantis,

               1 as compteur,

               version,

               severite,     

               date_changement ,

               Year(date_changement ) as Année,

               Month(date_changement ) as Mois,

               Week (date_changement ) as Semaine,

               Day (date_changement ) as Jour,

               Hour (date_changement ) as theures,

               Minute (date_changement ) as minutes,

               Hour (date_changement )as heure,

               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,

               WeekDay (date_changement ) as JourSemaine,

               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, 

                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,

                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,

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

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

             

               Statut

           

           

          FROM

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

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