1 Reply Latest reply: Aug 15, 2017 5:15 PM by Nicole Smith RSS

    Cross Tab Format

    Saima SM

      Hi All,

       

      I have the following table

         

      YearQuarterMonthF1F2F3F4F5
      FY-2017/18Q1April010
      FY-2017/18Q1May000
      FY-2017/18Q2June000

       

       

      I want to transpose it like following Sample data is attached). can anybody help please? thanks.

       

         

      Required Table
      YearQuarterMonthCategory
      FY-2017/18Q1AprilF1
      FY-2017/18Q1AprilF2
      FY-2017/18Q1AprilF3
      FY-2017/18Q1AprilF4
      FY-2017/18Q1AprilF5
      FY-2017/18Q1MayF1
      FY-2017/18Q1MayF2
      FY-2017/18Q1MayF3
      FY-2017/18Q1MayF4
      FY-2017/18Q1MayF5
      FY-2017/18Q2JuneF1
      FY-2017/18Q2JuneF2
      FY-2017/18Q2JuneF3
      FY-2017/18Q2JuneF4
      FY-2017/18Q2JuneF5
        • Re: Cross Tab Format
          Nicole Smith

          This load script:

          Data:
          CrossTable (Category, Flag, 3)
          LOAD Year,
              Quarter,
              Month,
              F1,
              F2,
              F3,
              F4,
              F5
          FROM sample.xlsx (ooxml, embedded labels, header is 1 lines, table is Sheet1);
          
          

           

          Gives this output:

          Year Quarter Month Category Flag
          FY-2017/18Q1AprilF10
          FY-2017/18Q1AprilF21
          FY-2017/18Q1AprilF30
          FY-2017/18Q1AprilF4
          FY-2017/18Q1AprilF5
          FY-2017/18Q1MayF10
          FY-2017/18Q1MayF20
          FY-2017/18Q1MayF30
          FY-2017/18Q1MayF4
          FY-2017/18Q2JuneF10
          FY-2017/18Q2JuneF20
          FY-2017/18Q2JuneF30
          FY-2017/18Q2JuneF4
          FY-2017/18Q2JuneF5