Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
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;
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;
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
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