3 Replies Latest reply: Jan 21, 2013 2:12 PM by Vishwaranjan Kumar RSS

    Crosstable

      Hi,

       

      I'm trying to create a table in the format :

      Year, Item, Value/Qty and Month

      At the moment it has a value for each month going horizontally instead a long skinny format.

      It is currently in the format:

       

      YearItemSMQ01SMQ02SMQ03SMQ04SMQ05SMQ06SMQ07SMQ08SMQ09SMQ10SMQ11SMQ12
      2010a00000190078000000
      2011a000183020450019512285132516911700
      2012a83210391039000000000

       

      Required format :

      YearItemMonthValue
      2012a1832
      2012a21039
      2012a31039

       

      Any ideas?

       

      Many thanks

        • Re: Crosstable
          renjith pl

          Try

           

          Root:
          CrossTable(Month, Value, 2)
          LOAD Year,
               Item,
               SMQ01,
               SMQ02,
               SMQ03,
               SMQ04,
               SMQ05,
               SMQ06,
               SMQ07,
               SMQ08,
               SMQ09,
               SMQ10,
               SMQ11,
               SMQ12
          FROM
          crosstable.xlsx
          (ooxml, embedded labels, table is Sheet1);

          New:
          load
          *,
          Right(Month, 2) as MonthNew
          Resident Root;
          drop Table Root;

          • Re: Crosstable
            Vishwaranjan Kumar

            hi

            try this,

            a:

            CrossTable(month, value, 2)

            LOAD year,

                 item,

                 SMQ01,

                 SMQ02,

                 SMQ03,

                 SMQ04,

                 SMQ05,

                 SMQ06,

                 SMQ07

            FROM

            C:\crosstable.xlsx

            (ooxml, embedded labels, table is Sheet1);

            b:

            load

            value,

            Right(month, 2) as month2

            Resident a;

            drop Table a;

             

            hope this helps you.

            • Re: Crosstable
              Vishwaranjan Kumar

              hi

              please see the attached file.this file show the output like this

               

              Required format :

              YearItemMonthValue
              2012a1832
              2012a21039
              2012a31039