Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Transpose data horizontal to vertical format

 

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)

Tags (1)
1 Solution

Accepted Solutions

Re: Transpose data horizontal to vertical format

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 Replies

Re: Transpose data horizontal to vertical format

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;

Not applicable

Re: Transpose data horizontal to vertical format

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

Not applicable

Re: Transpose data horizontal to vertical format

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

Community Browser