3 Replies Latest reply: Dec 30, 2013 3:55 PM by Massimo Grossi RSS

    Merge rows with common fields and verifying conditions

      Hi

       

      I wish to write a clean up script that merges fields that verify

      1/ fields A and B are common to both rows

      2/ fields C and D are N/A for one row and equals field B for the other row

       

      Ex.


      A              B                C              D

      Val1          Val2           N/A           N/A
      Val1          Val2           Val2          Val2

       

      then in the first row, fields C and D should be filled with value Val2.

       

      I cannot find out how to write a condition on two rows (or write a condition with a search on the other rows inside)

       

      Thanks

        • Re: Merge rows with common fields and verifying conditions
          Massimo Grossi

          Hi

           

          for 2 rows you can try something similar to

           

          Tmp:

          LOAD * INLINE [

          A   ,           B    ,            C    ,          D,

          Val1,          Val2,           N/A,           N/A

          Val1,          Val2 ,          Val2,          Val2

          ];

           

          Table:

          load

          *,

          if(Peek(A)=A and Peek(B)=B and C='N/A' and D='N/A' and peek(C)=Peek(B) and peek(D)=Peek(B), Peek(C), C) as NewC,

          if(Peek(A)=A and Peek(B)=B and C='N/A' and D='N/A' and peek(C)=Peek(B) and peek(D)=Peek(B), Peek(D), D) as NewD

          resident Tmp

          order by A, B, C desc, D desc;

           

          DROP Table  Tmp;

           

           

           

          but what if you have more than 2 rows? example

           

          A              B                C              D

          Val1          Val2           N/A           N/A
          Val1          Val2           Val2          Val2

          Val1          Val2           N/A           N/A


          which result do you want?

          • Re: Merge rows with common fields and verifying conditions
            Massimo Grossi

            Try this;

            perhaps you have to change N/A in something else to be sure the order by works

             

            Tmp:

            LOAD * INLINE [

            A  ,          B    ,            C    ,          D,

            Val1,          Val2,          N/A,          N/A

            Val1,          Val2 ,          Val2,          Val2

            Val1,          Val2,          N/A,          N/A

            Val1,          Val3,          N/A,          N/A

            Val1,          Val3 ,          Val3,          Val3

            Val1,          Val3,          N/A,          N/A

            ];

             

             

            Table:

            noconcatenate load

            rowno(),

            A,

            B,

            if(Peek(A)=A and Peek(B)=B and C='N/A' and D='N/A' and peek(C)=Peek(B) and peek(D)=Peek(B), Peek(C), C) as C,

            if(Peek(A)=A and Peek(B)=B and C='N/A' and D='N/A' and peek(C)=Peek(B) and peek(D)=Peek(B), Peek(D), D) as D

            resident Tmp

            order by A, B, C desc, D desc;

             

             

            DROP Table  Tmp;