3 Replies Latest reply: Jun 27, 2016 11:34 AM by barry WIlmer RSS

    Transpose data horizontal to vertical format

    barry WIlmer

       

      I have a spreadsheet and the data is ‘horizontal’ (see dia 1) and I need to transpose the data to a vertical format (see dia 2).

      Attached is a sample spreadsheet

       

      I have tried Load inline but it does not work, any ideas please

       

      Dia 1 (input)

       

      Dia 2 (output)

        • Re: Transpose data horizontal to vertical format
          Manish Kachhia
          For i = 1 to 4
            Table:
            LOAD BuyerBranchID, 
                AccountCodeGroup, 
                [Approval Req Above], 
                [Limit $(i)] as Limit,
                [Approver $(i)] as Approver
              
            FROM
            [Decider_ SMALL.xlsx]
            (ooxml, embedded labels, table is [Input (2)]);
          
          
          Next i;
          
          • Re: Transpose data horizontal to vertical format
            Rohan DSouza

            Hi Barry,

             

            You will have to break down the original load statement and concatenate them.

            Something like this:

             

            LoadBuyer:

            LOAD BuyerBranchID,

                AccountCodeGroup,

                [Approval Req Above],

                [Limit 1] as Limit,

                [Approver 1] as Approver

            FROM

            [C:\Users\rohan.dsouza\Desktop\Decider_ SMALL.xlsx]

            (ooxml, embedded labels, table is Input);

             

             

            Concatenate

            LOAD BuyerBranchID,

                AccountCodeGroup,

                [Approval Req Above],

                [Limit 2] as Limit,

                [Approver 2] as Approver

            FROM

            [C:\Users\rohan.dsouza\Desktop\Decider_ SMALL.xlsx]

            (ooxml, embedded labels, table is Input);

             

             

            Concatenate

            LOAD BuyerBranchID,

                AccountCodeGroup,

                [Approval Req Above],

                [Limit 3] as Limit,

                [Approver 3] as Approver

            FROM

            [C:\Users\rohan.dsouza\Desktop\Decider_ SMALL.xlsx]

            (ooxml, embedded labels, table is Input);

             

            Concatenate

            LOAD BuyerBranchID,

                AccountCodeGroup,

                [Approval Req Above],

                [Limit 4] as Limit,

                [Approver 4] as Approver

            FROM

            [C:\Users\rohan.dsouza\Desktop\Decider_ SMALL.xlsx]

            (ooxml, embedded labels, table is Input)

            ;

             

            NoConcatenate

            LoadAccount:

            Load *

            Resident [LoadBuyer]

            order by [AccountCodeGroup],[Limit] asc;

             

            drop table LoadBuyer;

             

             

            Regards,

            Rohan