topic Convert column headings into rows in QlikView

Convert column headings into rows

shannonmarshall — Thu, 07 Jul 2016 19:26:45 GMT

Hello,

I have a table that has various columns with data. Five of those columns I would like to make into a single column and use the headings as the row data. Below is an example of what I am talking about. Is there a way to code this to work? I do not think an Inline table would work because the data can have multiple values. Any help is greatly appreciated.

The file I am trying to change is a QVD. Is there a way for me to create another table to have the data the way I want and then join back to the original table to bring in the other data values I need?

Thanks

Shannon

Re: Convert column headings into rows

boorgura — Thu, 07 Jul 2016 20:15:20 GMT

You can use the transpose option while reading from the source.

Re: Convert column headings into rows

trdandamudi — Thu, 07 Jul 2016 20:23:30 GMT

Use"Transform" option and you will get it. Attached is the qlikview and sample excel data file for the reference:

Re: Convert column headings into rows

MarcoWedel — Thu, 07 Jul 2016 22:08:29 GMT

Hi,

another solution could be a crosstable load, especially when having more than one row of persons in your source table:

table1:

CrossTable (Role, User)

LOAD RecNo() as %Key, *

INLINE [

Fiscal Contract, Fiscal Officer, Approver, Project Manager, Project Examiner

Person A, Person B, Person C, Person B, Person B

Person D, Person E, Person F, Person D, Person E

Person G, Person G, Person H, Person I, Person J

];

Re: Convert column headings into rows

shannonmarshall — Fri, 08 Jul 2016 12:10:14 GMT

My apologies on my post. The file I am trying to update is not an excel file. I just used excel to show what I wanted to do. The data is in a QVD that I am trying to manipulate.

Thanks

Re: Convert column headings into rows

Fri, 08 Jul 2016 12:20:15 GMT

Hai,

Actually I have no idea how to upload inline table, you can change it in your excel sheet like this. use the past special option.

ex: - copy the all and use past special

Re: Convert column headings into rows

boorgura — Fri, 08 Jul 2016 13:38:32 GMT

My apologies - forgot that transform will only work on the data and not on the columns!

You will have to read from QVD - store to a temp excel file, and then you read from excel (with no header) and tranpose it!

Re: Convert column headings into rows

boorgura — Fri, 08 Jul 2016 13:48:46 GMT

Or use cross tab on the QVD load -->

Test:

LOAD [Fiscal Contact],

[Fiscal Officer],

Approver,

[Project Manager],

[Project Examiner]

FROM

Test_Transpose.qvd

(qvd);

final:

CrossTable(Role, User)

LOAD

'' as Test,

[Fiscal Contact],

[Fiscal Officer],

Approver,

[Project Manager],

[Project Examiner]

Resident Test;

DROP Table Test;

drop Field Test;