11 Replies Latest reply: Sep 29, 2017 5:19 AM by susant Kumar swain RSS

    Grouping rows

    Neal Gosz

      Hello,

      I'm trying to group a series of rows and tried using which I thought should be fairly simple in the attachment.

       

      Table

      ID

      max(4 Week Pct Value),

      max(8 Week Pct Value),

      max(12 Week Pct Value)

      Group by ID;

       

      But in the grouping, I get 3 rows back instead of 1.  I am looking into the underlying data for the long term solution, but in the short-term is there something I can do in the script or an expression to group the data together into one row?

       

      I also tried using the peek() function looking at the Previous ID.

       

      Thanks in advance

        • Re: Grouping rows
          Sunny Talwar

          What was the exact script used?

            • Re: Grouping rows
              Neal Gosz

              Here's the script:

               

              Original_Table

              Load

                   ID,

                   Name,

                   Department_ID

                   [4 Week Target Flag],

                   [8 Week Target Flag],

                   [12 Week Target Flag],

                   [4 Week Target Value],

                   [8 Week Target Value],

                   [12 Week Target Value]

              ;

               

              MAX_VALUE:

              load ID,

                   max([ 4 Week Target Value]) as [4 Week Target Value test],

                   max([8 Week Target Value]) as [8 Week Target Value test],

                   max([12 Week Target Value]) as [12 Week Target Value test]

               

              resident Original_Table

              group by IID

              ;

                • Re: Grouping rows
                  Sunny Talwar

                  Do you drop your original_table after MAX_VALUE before you check the result on the front end?

                    • Re: Grouping rows
                      Neal Gosz

                      I don't - there are 7-8 other fields in the original table.  Would I have to apply a mass group by the dimension fields?

                        • Re: Grouping rows
                          Sunny Talwar

                          May be try this

                           

                          Original_Table:

                          Load

                               ID,

                               Name,

                               Department_ID

                               [4 Week Target Flag],

                               [8 Week Target Flag],

                               [12 Week Target Flag],

                               [4 Week Target Value],

                               [8 Week Target Value],

                               [12 Week Target Value]

                          FROM ...;

                           

                          MAX_VALUE:

                          load ID,

                               Only(Name) as Name,

                               Only(Department_ID) as Department_ID,

                               Max([ 4 Week Target Value]) as [4 Week Target Value],

                               FirstSortedValue([4 Week Target Flag], -[4 Week Target Value]) as [4 Week Target Flag],

                               Max([8 Week Target Value]) as [8 Week Target Value],

                               FirstSortedValue([8 Week Target Flag], -[8 Week Target Value]) as [8 Week Target Flag],

                               Max([12 Week Target Value]) as [12 Week Target Value],

                               FirstSortedValue([12 Week Target Flag], -[12 Week Target Value]) as [12 Week Target Flag],

                          Resident Original_Table

                          Group By ID;

                           

                          DROP Table Original_Table;

                  • Re: Grouping rows
                    Vineeth Pujari

                    What does the input data look like?

                     

                    You getting separate rows only if there are other columns that have different values and when your group by all the other fields it's like creating a pivot table in excel!! they will come up in separate lines

                      • Re: Grouping rows
                        Neal Gosz

                        The Excel attachment is what I'm looking at currently.

                          • Re: Grouping rows
                            Vineeth Pujari

                            Just do a pivot in excel itself and you will understand why you are getting 3 separate rows

                             

                                    

                            IDName4 Week Flag8 Week Flag12 Week FlagOrdered DateMax of Pct ValueMax of Pct Value2Max of Pct Value3
                            11ID DescriptionNothingN Nothing14-02-2017064.4
                            11ID DescriptionNothingNothingNothing14-02-201700
                            11ID DescriptionNothingNothingY14-02-20170096.6
                            Grand Total064.496.6
                            • Re: Grouping rows
                              Vineeth Pujari

                              You are grouping by 3 different combinations

                               

                               

                              ID | Name | 4 Week Flag | 8 Week Flag | 12 Week Flag | Ordered Date

                              11 | ID Description | Nothing | N  | Nothing| 42780

                              11 | ID Description | Nothing | Nothing | Nothing| 42780

                              11 | ID Description | Nothing | Nothing | Y | 42780