7 Replies Latest reply: Mar 21, 2013 8:42 AM by Paul Furrer RSS

    Switching column-data into rows

      Dear all

       

      I have following source table where an undefined number of identical data in col. A could exist and other data in col. B (current example shows 3x '1' and twice '2' in col. A)

       

      Source.JPG

       

      I tried to get following target table upon loading the file, but neither succeeded myself nor found any solution.

       

      Target.JPG

       

      Does anybody have an idea?

       

      Thanks a lot for your help.

       

      Paul

        • Re: Switching column-data into rows
          Alessandro Saccone

          I have the following idea:
          suppose that the table is myTable with columns number and data

          T1:
          load distinct number from myTable

          for i = 1 to nrows() -- test this condition
          let value = peek('number',$(i),T1)

          left join

          load
          number,
          data
          from myTable
          where number = $(value);


          next

          n times as the number of rows in T1

          • Re: Switching column-data into rows

            Try this, it will get you ptretty close....

             

            tmpTable:

             

             

            LOAD * INLINE [

                Number, Data

                1, 1

                1, 2

                2, 3

                3, 4

                2, 4

                4, 5

                5, 6

                6, 7

                7, 8

                8, 9

                1, 10

                9, 12

            ]
            ;

            //

            //Count_Table:

            //Load Number,

            //Count (Number) as count

            //resident tmpTable group by Number;



            let cols = 5;



            Table:

            Noconcatenate Load *,

            Number &'-' & Data as Key

            resident tmpTable order by Number, Data;



            drop table tmpTable;



            FINAL_TABLE:

            Load if (Number<>Previous(Number), Number) as New_Num,

            if (Number<>Previous(Number), Data) as 1,

            if (Number<>Previous(Number), Key) as New_Key

            resident Table;



            left join (Table) Load

            New_Key as Key,

            '1'
            as read

            Resident FINAL_TABLE;





            New_Table:

            NoConcatenate Load Number,

            Data,

            Key

            Resident Table where IsNull(read)=-1;



            drop table Table;

            Rename Table New_Table to Table;



            //drop table Count_Table;



            for i=2 to $(cols)





            Concatenate (FINAL_TABLE) Load

            if (Number<>Previous(Number), Number) as New_Num,

            if (Number<>Previous(Number), Data) as $(i),

            if (Number<>Previous(Number), Key) as New_Key

            resident Table;





            left join (Table) Load

            New_Key as Key,

            '1'
            as read

            Resident FINAL_TABLE;



            New_Table:

            NoConcatenate Load Number,

            Data,

            Key

            Resident Table where IsNull(read)=-1;



            drop table Table;

            Rename Table New_Table to Table;





            next



            drop table Table;

            drop field New_Key;

             

            • Re: Switching column-data into rows
              Gysbert Wassenaar

              If you can live with the result being a pivot table then have a look at the attached qvw. The underlying data will have only three fields, but the pivot table looks like you want afaict.

                • Re: Switching column-data into rows

                  Dear Gysbert

                  Thanks a lot. Your code is really great and helped me to finalize my task which was to LEFT JOIN your resulting table to another Excel-file with various and different "Number".

                  I would like to quote the whole code here, but as I am new to the community, I don't know how to do that nor how to attach a QVW-file and could not find anything searching the community. Sorry.

                  Kind regards

                  Paul

                   

                  Result after final completion of task:

                  Final_result.JPG