Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our May 2021 end-to-end product release from Data Integration to Data Analytics is out! READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
c_pannese
Partner
Partner

transpose a table, rows and columns.

Hello,

i'd like to transpose the structure of an excel table.

My table has this aspect:

DATEJEN
FEBMARAPRMAYJUNJULAUGSEP
#MEASURE1
#MEASURE2
#MEASURE3
#MEASURE4
#MEASURE5
#MEASURE6

and i'm trying to realize somthing like this

DATE#MEASURE1#MEASURE2#MEASURE3#MEASURE4#MEASURE5#MEASURE6
JAN
FEB
MAR
APR
MAY
JUN
JUL
AUG

SEP

Now, i tought to use crosstable  statement first and elaborate my new table starting from this discussion:

cross table in Qlik Sense

it seems work but not correctly because the same row is repeted many time instead of only one.

i expect to see only one row for month.

Have any  suggestion?

Thanks a lot.

10 Replies
StuartMoncrieff
Contributor
Contributor

Thanks for this, but I feel like this solution is a bit unworkable for a table in which you don't know what all the field names will be.  For example a table I am looking at has two columns - one called variableName and another with variableValue.  Not the best table I know, but this is what I have to work with.

So if my table is hundreds of lines long, with an unknown number of different variable names, I wouldn't be able to list them all in the data load editor.

I was hoping to find something like the proc transpose which SAS offers, but in the Qlik Sense universe.  That is pull in the data, make the variableName values the headers for the resulting table and the values populate.  

e.g.

id | variableName | variableValue

001 | name | Adam

001 |age|33

001|city|London

002 |name| Sarah

002|age|34

002|city|Paris

becomes...

id | name | age| city

001 | Adam | 33 | London

002 | Sarah | 34 | Paris

But this is for a table of unknown size with an unknown number of variable names.