5 Replies Latest reply: Jun 19, 2016 7:29 AM by Stefan Wühl RSS

    Select only() on field based on Recno but Garbage after Group BY

    Kurt Gulman

      Hey there,

       

      so I have this script, the description is unique to the rec num, and I can bring it in fine, but when I try to group by I get the garbage error. Here is the code below, am I approaching this incorrectly?

       

      Garbage after statement

      Test:

      Load

        %CO_Area,

        %Cost_Center,

        only([Cost Center Description]),

        only(KeepChar(Right(%Cost_Center,4) & '-' & %CO_Area & [Cost Center Description],[Cost Center Description])) As Description,

        RecNo() As RecNum

       

        Group By

        RecNum

       

      FROM

      //Another QVD

        • Re: Select only() on field based on Recno but Garbage after Group BY
          Stefan Wühl

          a) the GROUP BY clause should be put after the FROM

           

          b) RecNum is created in the LOAD statement, so it's not available as field name from the input table source

           

          c) you would need to apply aggregation functions to all fields not listed in the GROUP BY (e.g. there are missing aggregations for %CO_Area, %Cost_Center

           

          d) why do you want to group by Recno() at all?

            • Re: Select only() on field based on Recno but Garbage after Group BY
              Kurt Gulman

              Thank you so much for your input

               

              see what is happening is, probably best illustrated by an example

               

              a CO_AREA and Cost Center (ex.. 1010-1111 might have a description of EXAMPLE1

              and another CO_AREA and Coster (ex.1020-1111 might have a description of EXAMPLE2

               

              if I just load Cost Center Descriptions as is, both of these two rows/records will show EXAMPLE2 and I am hoping there might be a way to make sure each individual cost center description is associated with each row when imported from the QVD

               

              Hope it explains it better!

                • Re: Select only() on field based on Recno but Garbage after Group BY
                  Kurt Gulman

                  Oh and a quick follow up, where could I put the RecNo() function to count the records from the QVD and use it in the group by?, I can't do it in the load right, if I just load it I get the rec numbers fine and no crash and can see the column

                   

                  just adding the group by crashes it, I tried aggregating the rest and then it tells me that RecNum is not a valid field or something to that effect haah

                   

                  Once again thanks for your input!

                  • Re: Select only() on field based on Recno but Garbage after Group BY
                    Stefan Wühl

                    Not sure what you are trying to achieve, but IMHO, it doesn't make much sense to group by record number.

                     

                    Your code does not seem to return the same Description without the group by clause, though I might not use input records similar to yours.

                     

                    Load

                      %CO_Area,

                      %Cost_Center,

                      [Cost Center Description],

                      KeepChar(Right(%Cost_Center,4) & '-' & %CO_Area & [Cost Center Description],[Cost Center Description]) As Description

                    INLINE [

                      %CO_Area,  %Cost_Center,Cost Center Description

                      1010, 1111,EXAMPLE1

                      1020, 1111,EXAMPLE2

                    ];