5 Replies Latest reply: Sep 28, 2012 12:47 AM by Rajni Batra RSS

    Error in Script

    Arturo Javier Torres

      hello i need help with this LOAD.. the highlight red is the problem when delete this part all go fine, but i need calculated this camp  i appreciate any idea?

       

      Tiempos:

      LOAD

      //[Fecha Creacion] as Fec,

      ActionPlanID,

      //DocumentType as DT,

      //IssueTrackDisplayStatus AS ITDS,

      //[Estatus Plan Acción] as EPA,

      //WMRDisplayStatus as WMR,

      IF(DocumentType='ActionPlan' and IssueTrackDisplayStatus='False' and [Estatus Plan Acción]= 'Open',

          If(DocumentType='ActionPlan' and WMRDisplayStatus='True' and Today(0)-[Fecha Creacion]>15, '1', '0'),'') AS Tiempo1,

      IF(DocumentType='ActionPlan'and IssueTrackDisplayStatus='False'and [Estatus Plan Acción]= 'Open',

          IF(WMRDisplayStatus='True' and Today()-[Fecha Creacion]<25,'No Vencido',

              IF(TextCount([Plan de Acción])= 0 and Today()-[Fecha Creacion]>25,'PA no Capturado','Vencido')),'')AS Tiempo2,

      IF(DocumentType='ActionPlan'and [Estatus Plan Acción]='Open' and IssueTrackDisplayStatus='True',

          IF ([Fecha Compromiso]< Today() or [Fecha Compromiso Extendida]< Today(),'Vencido','No Vencido')

          ,'') AS Tiempo3,

      IF(DocumentType='ActionPlan'and [Estatus Plan Acción]='Open - Ready for Review',

          IF (Today()-[Fecha Compromiso]>10 or  Today()-[Fecha Compromiso Extendida]>10,'Vencido','No Vencido')

          ,'')AS Tiempo4

      Resident BaseDatos

      /*Where DocumentType='ActionPlan' or */Where Len(ActionPlanID)>0;

        • Re: Error in Script
          Rajni Batra

          hi,

          TEXTCOUNT function u r using is a aggregation function which need to be precced be GROUP BY clause..

          Thts why u r getting error in ur script.

          for more info see QV help...

          Hope to Help

            • Re: Error in Script
              Arturo Javier Torres

              Hello rajni

               

              let me clear why i use the "textcount" i have this sentence in an expression into the table and works fine, can you me explain how to use the "group by" in this case? i revised the help but not me cleared all.

              I'm new in QV and i like learn different forms to solved the things...

               

              thank you very much in advance

               

              ...

                • Re: Error in Script
                  Rajni Batra

                  here are few examples:

                  Load ArtNo, round(Sum(TransAmount),0.05) as ArtNoTotal from table.csv

                  group by ArtNo;

                  Load Week, ArtNo, round(Avg(TransAmount),0.05) as WeekArtNoAverages

                  from table.csv group by Week, ArtNo;

                  if ur problem is resolved by len(trim()) that is nice ...

                  but if want to understand group by then u can study he examples.

                  basically we use group by if our aggregation depends on something like month wise sales

                  so we will use

                  sum(Sales)

                  group by Month;

                  but if u have more fields in ur script it will give u error as in ur script

                  so u have to grooup by with all the fields in which no aggregation is applied...

                  Hope to help

              • Re: Error in Script
                Henric Cronström

                Rajni is right. TextCount is probably not the function you want to use. If you want to test the length of the field, you should use Len() or Len(Trim()).

                 

                HIC

                  • Re: Error in Script
                    Arturo Javier Torres

                    i changed len for textcount and work fine, thank you and i appreciate for help

                     

                    Tiempos:

                    LOAD

                    //[Fecha Creacion] as Fec,

                    ActionPlanID,

                    //DocumentType as DT,

                    //IssueTrackDisplayStatus AS ITDS,

                    //[Estatus Plan Acción] as EPA,

                    //WMRDisplayStatus as WMR,

                    IF(DocumentType='ActionPlan' and IssueTrackDisplayStatus='False' and [Estatus Plan Acción]= 'Open',

                        If(DocumentType='ActionPlan' and WMRDisplayStatus='True' and Today(0)-[Fecha Creacion]>15, '1', '0')

                        ,'') AS Tiempo1,

                    IF(DocumentType='ActionPlan'and IssueTrackDisplayStatus='False'and [Estatus Plan Acción]= 'Open',

                        IF(WMRDisplayStatus='True' and Today()-[Fecha Creacion]<25,'No Vencido',

                            IF(Len([Plan de Acción])= 0 and Today()-[Fecha Creacion]>25,'PA no Capturado','Vencido'))

                            ,'') AS Tiempo2,

                    IF(DocumentType='ActionPlan'and [Estatus Plan Acción]='Open' and IssueTrackDisplayStatus='True',

                        IF ([Fecha Compromiso]< Today() or [Fecha Compromiso Extendida]< Today(),'Vencido','No Vencido')

                        ,'') AS Tiempo3,

                    IF(DocumentType='ActionPlan'and [Estatus Plan Acción]='Open - Ready for Review',

                        IF (Today()-[Fecha Compromiso]>10 or  Today()-[Fecha Compromiso Extendida]>10,'Vencido','No Vencido')

                        ,'')AS Tiempo4

                    Resident BaseDatos

                    /*Where DocumentType='ActionPlan' or */Where Len(ActionPlanID)>0;