Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Transpose issue

Hi

My table looks like this:

Header 1Header 2Header 3
text12012100
text12013200
text22014300
text22015400

I need to make my table look like this:

Header 12012201320142015
text1100200--
text2--300400

Right now my script looks like:

If ([Header 2]=2012,[Header 3]) as 2012,

.

.

.

and so on.

And the result looks like:

Header 12012201320142015
text1100---
text1-200--
text2--300-
text2---400

Any ideas on why this happens?

Thank you!

2 Replies

Re: Transpose issue

You have four source records and you end up with four records because you're not aggregating anything. I don't know why you think you need to transpose the data. In the front end you can simply use a pivot table with Header1 and Header2 as dimensions and sum(Header3) as expression to get the result you want. But if you feel that you need to do this in the script then you need to add an extra load statement to create the result table:

Result:

NOCONCATENATE LOAD

     Header1,

     Sum(2012) as 2012,

     Sum(2013) as 2013,

     Sum(2014) as 2014,

     Sum(2015) as 2015

Resident YourFirstTable

Group By Header1;

Drop Table YourFirstTable;

Replace YourFirstTable with the name of the table you now create with your if- statements.


talk is cheap, supply exceeds demand
Not applicable

Re: Transpose issue

Thank you Gysbert for the prompt answer, much appreciated! I couldn`t use a pivot table because the database has more than 10 million rows x 25 columns and calculations take a lot of time. This is why I wanted to do most of the job done at the script level and display the results in a basic table. Meanwhile we temporarily solved the issue by taking only 2015 into account.

Have a great day!

Community Browser