Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ts3st1000
New Contributor

Transpose Table

Hi, I am displaying a table looking like the following:

 

MaturitySales product1Sales product2Sales product3
1 year5,9138,133-2,223
2 year39,52538,560929
3 year41,09439,8721,188
4 year97,90479,5164,737
5 year309,085290,6129,810
6 year85,371109,4812,710

That I would like to transpose to look like this:

    

Maturity1 year2 year3 year4 year5 year6 year
Sales product15,91339,52541,09497,904309,08585,371
Sales product28,13338,56039,87279,516290,612109,481
Sales product3-2,2239291,1884,7379,8102,710

Any suggestions to do this please. Thanks very much.

14 Replies
omarbensalem
Esteemed Contributor

Re: Transpose Table

Create a pivot table:

Capture.PNG

as a dimension:

=ValueList('Sales Product1','Sales Product2','Sales Product3')

as a measure:

if(ValueList('Sales Product1','Sales Product2','Sales Product3')='Sales Product1', sum([Sales product1]),

if(ValueList('Sales Product1','Sales Product2','Sales Product3')='Sales Product2', sum([Sales product2]),

if(ValueList('Sales Product1','Sales Product2','Sales Product3')='Sales Product3', sum([Sales product3]))))

add a line:

Maturity

RESULT:

Capture.PNG

ts3st1000
New Contributor

Re: Transpose Table

Thanks very much Omar. if i understood correctly, i go to charts, and select Pivot table

Capture.PNG

open it and fill it using the code you suggested by adding a dimension

Capture11.PNG

omarbensalem
Esteemed Contributor

Re: Transpose Table

Yes please

Re: Transpose Table

Script / Front end?

kaanerisen
Contributor III

Re: Transpose Table

Hi,

You can achieve that by just converting your table to pivot table and switch column and rows. That's all you need

Untitled.png

omarbensalem
Esteemed Contributor

Re: Transpose Table

Never thought this was  possible ! Thanks a lot !

saniyask
Contributor

Re: Transpose Table

Hi,

As Kaan rightly suggested, you can do it by simply creating a pivot chart and transposing your measures and Dimension.

But an alternate way to do this is in the script as well. PFA images for your reference.

Scripting :-

Output:-

Regards,

Saniya.

ts3st1000
New Contributor

Re: Transpose Table

Thanks Kaan. Can you please explain how to convert an existing table to pivot table ?

I have tried to create a new pivot table and flipped the columns into rows but did not get the right results. the reason being that the Sales Product columns don’t exist already in the tables, but I create them on the fly using formulas like

=sum(if([Product]='Product1', [Sales])) for Sales product1 column.

=sum(if([Product]='Product2', [Sales])) for Sales product2 column

=sum(if([Product]='Product3', [Sales])) for Sales product3 column

luismadriz
Valued Contributor

Re: Transpose Table

Hi,

You can just drag a Pivot Table over a Table as select Convert if you want to convert it but you may have to review the default design and may not get exactly what you want right away.

To create from scratch just try something simpler:

Dimension: Product

Measure: Sales and select Sum

Then Add Data (Column) and select Year

Then, if you want, you can restrict Sales to specific products by editing the Measure and changing it from

Sum(Sales)

to

Sum({<Product={"Product1","Product2","Product3"}>} Sales)

I hope this helps,

Luis