Transpose data horizontal to vertical format

[#1]

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)

[MK_QSL · Accepted solution]

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;

[#3]

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

(ooxml, embedded labels, table is Input);

Concatenate

LOAD BuyerBranchID,

    AccountCodeGroup,

    [Approval Req Above],

    [Limit 2] as Limit,

    [Approver 2] as Approver

FROM

(ooxml, embedded labels, table is Input);

Concatenate

LOAD BuyerBranchID,

    AccountCodeGroup,

    [Approval Req Above],

    [Limit 3] as Limit,

    [Approver 3] as Approver

FROM

(ooxml, embedded labels, table is Input);

Concatenate

LOAD BuyerBranchID,

    AccountCodeGroup,

    [Approval Req Above],

    [Limit 4] as Limit,

    [Approver 4] as Approver

FROM

(ooxml, embedded labels, table is Input)

;

NoConcatenate

LoadAccount:

Load *

Resident [LoadBuyer]

order by [AccountCodeGroup],[Limit] asc;

drop table LoadBuyer;

Regards,

Rohan

[#4]

Hi Manish

Thank you very much, works extremely well.

one last question

some of my records contain no data for the Limit / Approver

I have tried to test for Null and ''

I have also tried

WHERE Limit $(I) <> ''

but it seems to load the records regardless of the empty fileds

thanks again