Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
You can use the transpose option while reading from the source.
Use"Transform" option and you will get it. Attached is the qlikview and sample excel data file for the reference:
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
];
see also Crosstable ‒ QlikView
hope this helps
regards
Marco
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
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
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!
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;