3 Replies Latest reply: Sep 1, 2018 9:49 PM by surfman joe RSS

    transpose data when loading excel

    surfman joe

      Hello, I have one difficulty when loading excel data as attached.

       

      Basically the original data is this,

       

       

      SKUProspective2018 P01 W12018 P01 W22018 P01 W32018 P01 W42018 P02 W12018 P02 W22018 P02 W32018 P02 W42018 P02 W5
      120on hand units4,6404,6484,6414,3304,2774,2914,7544,4264,817
      120on hand cost50,26350,37350,31346,94846,37546,55751,55447,99852,242
      120pos units1,4291,7251,5111,5301,8121,7931,9932,3101,945
      120pos sales19,27323,26620,37920,63624,43924,18326,88031,15626,233
      25465on hand units121212121212202018
      25465on hand cost545454545454909081
      25465pos units200000402
      25465pos sales110000022011

       

      I want it loaded as this:

       

      SKUTimeon hand unitson hand costpos unitspos sales
      1202018 P01 W14,64050,2631,42919,273
      1202018 P01 W24,64850,3731,72523,266
      1202018 P01 W34,64150,3131,51120,379
      1202018 P01 W44,33046,9481,53020,636
      1202018 P02 W14,27746,3751,81224,439
      1202018 P02 W24,29146,5571,79324,183
      1202018 P02 W34,75451,5541,99326,880
      1202018 P02 W44,42647,9982,31031,156
      1202018 P02 W54,81752,2421,94526,233
      254652018 P01 W11254211
      254652018 P01 W2125400
      254652018 P01 W3125400
      254652018 P01 W4125400
      254652018 P02 W1125400
      254652018 P02 W2125400
      254652018 P02 W32090422
      254652018 P02 W4209000
      254652018 P02 W51881211

       

      obviously the time is expending week by week. can anyone help to write script? thanks in advance.

        • Re: transpose data when loading excel
          Quy Nguyen

          Hi,

          Try this script (rename the connection with your connection)

          A:
          CrossTable(Time, Value, 2)
          LOAD
              SKU,
              Prospective,
              "2018 P01 W1",
              "2018 P01 W2",
              "2018 P01 W3",
              "2018 P01 W4",
              "2018 P02 W1",
              "2018 P02 W2",
              "2018 P02 W3",
              "2018 P02 W4",
              "2018 P02 W5"
          FROM [lib://Desktop/transpose sample data.xlsx]
          (ooxml, embedded labels, header is 1 lines, table is Sheet1);
          
          
          B:
          Load SKU,
           Time,
               If(Prospective = 'on hand units',Value) As "on hand units",
               If(Prospective = 'on hand cost',Value) As "on hand cost",
               If(Prospective = 'pos units',Value) As "pos units",
               If(Prospective = 'pos sales',Value) As "pos sales"
          Resident A;
          Drop Table A;
          
          
          FinalData:
          NoConcatenate
          Load 
           SKU,
              Time,
              Sum("on hand units") As "on hand units",
              Sum("on hand cost") As "on hand cost",
              Sum("pos units") As "pos units",
              Sum("pos sales") As "pos sales"
          Resident B
          Group by SKU, Time;
          Drop Table B;