Transpose issue

[#1]

Hi

My table looks like this:

Header 1	Header 2	Header 3
text1	2012	100
text1	2013	200
text2	2014	300
text2	2015	400

I need to make my table look like this:

Header 1	2012	2013	2014	2015
text1	100	200	-	-
text2	-	-	300	400

Right now my script looks like:

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

.

.

.

and so on.

And the result looks like:

Header 1	2012	2013	2014	2015
text1	100	-	-	-
text1	-	200	-	-
text2	-	-	300	-
text2	-	-	-	400

Any ideas on why this happens?

Thank you!

[Gysbert_Wassenaar]

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.

[#3]

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!