7 Replies Latest reply: Sep 10, 2014 1:36 AM by pavan Kumar RSS

    How to merge matching rows

      Hi I have table like this

       

      A B C D E F Amount

      1 2 3 4 5 6   500

      1 2 3 4 5 6   500

      1 2 7 4 5 6   400

      1 2 7 4 5 6   400

      1 2 8 4 5 6   200

       

      I want to load this table by merging similar rows in to one  like this

      9

      A B C D E F   sum(Amount)

      1 2 3 4 5 6     1000

      1 2 7 4 5 6       800

      1 2 8 4 5 6      200 

       

      Thanks

      Pavan

        • Re: How to merge matching rows
          Nagaian Krishnamoorthy

          Try the following:

           

          LOAD * Inline [
          A,B,C,D,E,F,Amount
          1,2,3,4,5,6,  500
          1,2,3,4,5,6,  500
          1,2,7,4,5,6,  400
          1,2,7,4,5,6,  400
          1,2,8,4,5,6,  200
          ]
          ;
          Table2:
          NoConcatenate LOAD A,B,C,D,E,F,Sum(Amount) as Value
          Resident Table1 Group By A,B,C,D,E,F;

          • Re: How to merge matching rows
            Enrique Colomer

            In the script use Resident Group:

             

            ZZ:

            LOAD * Inline [

            A, B, C, D, E, F, Amount

            1, 2, 3, 4, 5, 6,   500

            1, 2, 3, 4, 5, 6,   500

            1, 2, 7, 4, 5, 6,   400

            1, 2, 7, 4, 5, 6,   400

            1, 2, 8, 4, 5, 6,   200

            ];

             

             

            XX:

            LOAD 

                A, B, C, D, E, F,

                sum(Amount) as TAmount

            resident ZZ

            group by A,B,C,D,E,F;

             

            DROP TABLE ZZ;

             

            p05.png

            • Re: How to merge matching rows
              Prashant Sangle

              Hi,

               

              If you want to do in script level then above solution must work for you.

               

              If you want in front end

              Simply take Straight table

              Add Dimension A,B,C,D,E,F

              and Write Expression Sum(Amount)

               

              It will give you desired result.

               

              for your reference i am attaching sample file also.

               

              Regards

              • Re: How to merge matching rows

                Thanks Friends

                 

                I have new problem now. I added new column G for the above table.

                 

                ZZ:

                LOAD * Inline [

                A, B, C, D, E, F, G,    Amount

                1, 2, 3, 4, 5, 6, 1,   500

                1, 2, 3, 4, 5, 6, 1,   500

                1, 2, 7, 4, 5, 6, 2,   400

                1, 2, 7, 4, 5, 6, 3,   400

                1, 2, 8, 4, 5, 6, 1,   200

                ];

                 

                I want to group by A B C D E F  But I want to see G rather all the remaining values when the user mouse over the Amount cell.

                • Re: How to merge matching rows
                  AVIRAL NAG

                  Try this:

                   

                  Table1:
                  Load * Inline [
                  A, B, C, D, E, F, Amount
                  1, 2, 3, 4, 5, 6, 500
                  1, 2, 3, 4, 5, 6, 500
                  1, 2, 7, 4, 5, 6, 400
                  1, 2, 7, 4, 5, 6, 400
                  1, 2, 8, 4, 5, 6, 200
                  ];

                   

                  Concatenate(Table1)

                  Load * Inline [
                  A, B, C, D, E, F, Amount
                  1, 2, 3, 4, 5, 6, 1000
                  1, 2, 7, 4, 5, 6, 800
                  1, 2, 8, 4, 5, 6, 200 
                  ];

                   

                  After this, Reload the Application.

                   

                  Use A,B,C,D,E,F as Dimensions

                  & Use Sum(Amount) as Expression.

                   

                  See the Attachment.

                   

                  Regards

                  Aviral Nag

                  • Re: How to merge matching rows

                    no like this when the user mouse over 1000

                     

                    it should pop up as

                    A, B, C, D, E, F, G,    Amount

                    1, 2, 3, 4, 5, 6, 1,   500

                    1, 2, 3, 4, 5, 6, 1,   500


                    If G column different also it should display like this


                    A, B, C, D, E, F, G,    Amount

                    1, 2, 3, 4, 5, 6, 1,   500

                    1, 2, 3, 4, 5, 6, 2 ,  500