Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
i'd like to transpose the structure of an excel table.
My table has this aspect:
DATE | JEN | FEB | MAR | APR | MAY | JUN | JUL | AUG | SEP | |||
---|---|---|---|---|---|---|---|---|---|---|---|---|
#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:
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.
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.