7 Replies Latest reply: Nov 26, 2012 10:21 AM by Michael Ionkin RSS

    Group by lead to invalid expression

    Michael Ionkin

      Hello,

       

      I have a problem with one load statement:

       

      I've figured already out that "group by lJobRef " is causing the problem... Please help

       

      Job_NEW_Plus_Min:
      LOAD lJobRef,
          
      dSetQuant,
          
      dSetShots,
          
      dSetSpeed,
          
      iActCavity,
          
      iSetCavity,
          
      Auftragsnummer,
          
      sType,
          
      tActBegin,
          
      tActEnd,
          
      tReleaseDate,
          
      Min(lTolRefArt) as lTolRefArt,
          
      Artikelbeschreibung,
          
      Artikel,
          
      Werkzeuglagerort,
          
      Werkzeugbeschreibung,
          
      Werkzeug,
          
      Maschinenplatz,
          
      Maschinenbeschreibung,
          
      Maschine,
           0
      as bZusatz

      FROM
      ..\..\02_Data\JOB_incl_ART_MASCHINE_WRZ.qvd
      (
      qvd)

      Where substringcount(Auftragsnummer,'+')>0
      group by lJobRef;
      Job_NEW_Plus_NICHT:
      LOAD lJobRef,
          
      dSetQuant,
          
      dSetShots,
          
      dSetSpeed,
          
      iActCavity,
          
      iSetCavity,
          
      Auftragsnummer,
          
      sType,
          
      tActBegin,
          
      tActEnd,
          
      tReleaseDate,
          
      lTolRefArt,
          
      Artikelbeschreibung,
          
      Artikel,
          
      Werkzeuglagerort,
          
      Werkzeugbeschreibung,
          
      Werkzeug,
          
      Maschinenplatz,
          
      Maschinenbeschreibung,
          
      Maschine,
           0
      as bZusatz

      FROM
      ..\..\02_Data\JOB_incl_ART_MASCHINE_WRZ.qvd
      (
      qvd)

      Where substringcount(Auftragsnummer,'+')=0 Group BY lJobRef;

        • Re: Group by lead to invalid expression
          Stefan Wühl

          From the HELP:

           

          group by is a clause used for defining over which fields the data should be aggregated (grouped). The aggregation fields should be included in some way in the expressions loaded. No other fields than the aggregation fields may be used outside aggregation functions in the loaded expressions.

           

          So either include all your loaded fields in the group by clause (NO, I wouldn't do this), or use aggregation functions with the ones not included.

            • Re: Group by lead to invalid expression
              Michael Ionkin

              So you mean that I can't use

              Load lJobRef

              ...

              ...

              Group By lJobRef

               

              Is that right?

               

              And if I still want to do it , I should before that load statement do sth like this

               

              Load lJobRef as lJobRefForAggr

              lJodRef

               

              And Later

               

              Load lJobRef

              ..

              ..

              Group by lJobRefForAggr

               

               

              Please correct if I'm wrong

               

              Thank You in advance

               

              //chesterluck

                • Re: Group by lead to invalid expression
                  Michael Ionkin

                  I just tried to solve the problem through

                   

                  Group by lJobRefForAggr

                   

                  But it leads to the same error "Invalid expression"

                   

                  //chesterluck

                  • Re: Group by lead to invalid expression
                    Stefan Wühl

                    No, that's not what I intended to say.

                     

                    You can't do something like

                     

                    LOAD A, B

                    resident TABLE group by A;

                     

                    You need to use aggregation functions with all fields that you want to load, but are not listed in the group by clause (i.e. B in this example).

                     

                    It's more or less the same as creating a table chart with dimension A, just inputting B as expression will not result in a something meaningful in most cases (unless the implicitely used only() function returns a value). Using group by in the script is just a little more strict, you need to explicitely use an aggregation function with B:

                     

                    LOAD

                         A,

                          sum(B)          // or some other script aggregation function (only(), min(), max(), avg() ....)

                    resident TABLE group by A;

                      • Re: Group by lead to invalid expression
                        Michael Ionkin

                          So you mean for this expression :

                         

                         

                        LOAD

                         

                        lJobRef,
                        dSetQuant,
                        dSetShots,
                        dSetSpeed,
                        iActCavity,
                        iSetCavity,
                        Auftragsnummer,
                        sType,
                        tActBegin,
                        tActEnd,
                        tReleaseDate,
                        MAx(lTolRefArt) as lTolRefArt,
                        Artikelbeschreibung,
                        Artikel,
                        Werkzeuglagerort,
                        Werkzeugbeschreibung,
                        Werkzeug,
                        Maschinenplatz,
                        Maschinenbeschreibung,
                        Maschine,
                        1
                        as bZusatz
                        FROM
                        ..\..\02_Data\JOB_incl_ART_MASCHINE_WRZ.qvd
                        (
                        qvd)
                        Where substringcount(Auftragsnummer,'+')>0

                         

                         

                        //I need a Group by:

                         

                         

                         

                        Group BYEVERYTHING except lTolRefArt as it is the aggr. field ;

                         

                         

                         

                        Right?

                          • Re: Group by lead to invalid expression
                            Stefan Wühl

                            Group by every Input field that is used in the load, right (so you don't need to group by bZusatz).

                             

                            But I think you don't really want to group by all these fields. In most cases, you only have some few fields you want to group by, that describe the relevant context you need for your aggregations.

                             

                            For example, you don't need any fields that are just detailing descriptions of some others (like maybe Maschine -> Maschinenbeschreibung). Then you don't want to group by any field that will restrict your context to portions too small (maybe when using timestamps).

                             

                            Just use the minimum required number of group by fields, then join your resulting table back on another table (or keep the tables separate but linked).

                             

                            Well, it's up to you how to design your data model.

                             

                            Regards,

                            Stefan