10 Replies Latest reply: Nov 5, 2014 7:51 AM by anbu cheliyan RSS

    Help on Column to Row

      Hi All,

       

      I have date in the following way.

       

      Product  Time1   Time2        Emx     Loc   Value    Tab1   Tab2   Tab3   Tab4  Col1   COl2   Col3 

      X           20-3-14   14-03-10   ABC     12      30        A        B        C       DD      1        2       3       

      Y          20-3-14   14-03-10   ABC     12      40         D        B        C       DD      7       4       5    

       

      Key can be formed on Product ,Time1  ,Time2 ,  Emx  ,Loc.

       

      Now I need Output with new Columns : Number, Tab. Col

       

      Product  Time1   Time2        Emx     Loc   Value    Number   Tab   Col

      X           20-3-14   14-03-10   ABC     12      30          1           A       1

      X           20-3-14   14-03-10   ABC     12      30          2           B       2

      X           20-3-14   14-03-10   ABC     12      30          3           C       3.

      Y          20-3-14   14-03-10   ABC     12      40          1           D       7

      Y          20-3-14   14-03-10   ABC     12      40          2           B        4

      Y          20-3-14   14-03-10   ABC     12      40          3           C        5


      So basically the values in Number field should be based on number of Tab's and COls. In my case I have three 3 Tabs and 3 Cols(Tab1, Tab2, Tab3) are there for all records so Number should be 1 2 3 .


      Can anyone help me on this.


      BR,

      Neehu

        • Re: Help on Column to Row
          sujeet singh

          I think this will do for you

           

          Cross tables are table where facts are divided on the basis of dimension values.

          for example we have sales for every month. then, if in our database we load sales amounts as a separated field for every month as

          Product         Jan                    Feb                    Mar   etc

          a                    100                    200                    300

           

          So, basically we have 1 single fact as sales amount here, and jan-dec are dimension memebers of month dimension but we are creating different fields for every dimension member and treating them as different field.

          Loading data this way, makes it difficult to properly aggregrate the data over different dimensions and difficult to do analysis.

          So, we convert this crosstable into a normal table.

          using

          crosstable (Month, Amount)

          load

          *

          from a.qvd(qvd);

           

          then we will get the same table as

           

          Product     Month     Amount

          a                    jan          100

          a                    feb          200

          a                    mar          300

           

           

           

           

          I hope you understand now, what is crosstable. let me know if it solves your concern.

           

          Regards,

          Sujeet

          • Re: Help on Column to Row
            anbu cheliyan

            Temp:

            Load * Inline [

            Product,Time1,Time2,  Emx,  Loc,Value, Tab1,Tab2,Tab3,Tab4,Col1,COl2,Col3

            X,20-3-14,14-03-10,ABC,  12,30,  A,  B,  C, DD,1,  2, 3

            Y, 20-3-14,14-03-10,ABC,  12,40,D,  B,  C, DD,7, 4, 5  ];

             

             

            Temp1:

            Load Product,Time1,Time2,  Emx,  Loc,Value,Tab1 As Tab,Col1 As Col Resident Temp;

            Concatenate

            Load Product,Time1,Time2,  Emx,  Loc,Value,Tab2 As Tab,COl2 As Col Resident Temp;

            Concatenate

            Load Product,Time1,Time2,  Emx,  Loc,Value,Tab3 As Tab,Col3 As Col Resident Temp;

             

             

            NoConcatenate

            Load *,AutoNumber(RecNo(), Product&'_'&Time1&'_'&Time2&'_'&  Emx&'_'&  Loc&'_'&Value) As Number Resident Temp1;

            Drop Tables Temp,Temp1;

              • Re: Help on Column to Row

                Hi Anbu,

                 

                Thank You.

                I will check and will let you know.

                  • Re: Help on Column to Row

                    But if My Tab and Col increases to Tab10 and COl10 then Should I write code(10 times concatenate) as per your above code?

                     

                    Will that be a fine?

                    Can you suggest me some script which checks dynamically and will do the needful without concatenating everytime.

                      • Re: Help on Column to Row
                        anbu cheliyan

                        If number of columns(Tab10 and COl10) won't change, then you can use 10 concatenate

                          • Re: Help on Column to Row

                            Ok. May i know why you have used Autonumber?

                            Because Autonumber will give random number. But i want 1 2 3 ,.... in Number field

                              • Re: Help on Column to Row
                                sujeet singh

                                How to handle n rows

                                • Re: Help on Column to Row
                                  anbu cheliyan

                                  Autonumber(RecNo(),RecNo()) along with RecNo() gives unique running number from 1 for each key. Check the result again, it gives correct result.

                                   

                                  LOad *,AutoNumber(RecNo(),Key) Inline [

                                  Key,Data

                                  1,11

                                  1,22

                                  2,33

                                  3,44

                                  3,55 ];

                                   

                                   

                                  You can also try like this, provided your column names follow naming convention like Tab[Number] and Col[Number]

                                   

                                  Temp:

                                  Load * Inline [

                                  Product,Time1,Time2,  Emx,  Loc,Value, Tab1,Tab2,Tab3,Col1,Col2,Col3

                                  X,20-3-14,14-03-10,ABC,  12,30,  A,  B,  C, 1,  2, 3

                                  Y, 20-3-14,14-03-10,ABC,  12,40,D,  B,  C, 7, 4, 5  ];

                                   

                                   

                                  Set vListOfColumns = ;

                                     For vColNo = 1 to NoOfFields('Temp')

                                        Let vColName = FieldName($(vColNo),'Temp') ;    

                                        If '$(vColName)' Like 'Tab*' Then

                                        Let vNo = PurgeChar('$(vColName)','Tab');

                                        Temp1:

                                    Load Product,Time1,Time2,  Emx,  Loc,Value,Tab$(vNo) As Tab,Col$(vNo) As Col Resident Temp;

                                     ENDIF

                                     Next vColNo

                                   

                                   

                                  NoConcatenate

                                  Load *,AutoNumber(RecNo(), Product&'_'&Time1&'_'&Time2&'_'&  Emx&'_'&  Loc&'_'&Value) As Number Resident Temp1;

                                  Drop Tables Temp,Temp1;

                        • Re: Help on Column to Row
                          sujeet singh

                          See this

                          Tab1:

                          LOAD * INLINE [

                              Product , Time1,   Time2   ,     Emx  ,   Loc , Value ,   Tab1 ,  Tab2   ,Tab3 ,  Tab4  ,Col1  , COl2 ,  Col3

                          X        ,   20-3-14 ,  14-03-10 ,  ABC    , 12     , 30     ,   A       , B    ,    C    ,   DD   ,   1   ,     2   ,    3      

                          Y    ,      20-3-14 ,  14-03-10 ,  ABC ,   , 12     , 40      ,   D     ,   B      ,  C     ,  DD    ,  7      , 4     ,  5   

                          ];

                           

                           

                          Tab2:

                          CrossTable(Tab,Y,1)

                          LOAD

                          Product , Tab1 ,  Tab2   ,Tab3 ,  Tab4

                          Resident Tab1;

                          //DROP table  Tab1;

                           

                          Tab3:

                          CrossTable(Col,X,1)

                          LOAD

                          Product, Col1  , COl2 ,  Col3

                          Resident Tab1;

                          DROP table  Tab1;