Repivot Excel Data in Load Script?

[#1]

Hiya Guys,

I have MS Excel data in the format:

Account	Account Name	January	February	March
1	My Account	100	200	350

To make this useable in QV - how do I Script a re-pivot so the data is loaded as:

Account	Account Name	Month	Amount
1	My Account	January	100
1	My Account	February	200
1	My Account	March	350

Surely this is fundamentally what QV is used for and this should be straight forward?! - or am I approaching the issue incorrectly?

Cheers,

Steve.

[Gysbert_Wassenaar · Accepted solution]

You have two columns you want in front, the months should end up in a field named Month and the amounts in a field named Amount. Use the crosstable function like this:

crosstable(Month,Amount,2)

load * from ...etc

See the load script of the attached qvw

[MayilVahanan]

HI

Use

crosstable in script ..

Please refer this:

http://community.qlik.com/message/279251#279251

for example

Hope it helps

[#4]

Use "Crosstable" option.

http://www.youtube.com/watch?v=qHu45iWamL8

[#5]

It's THAT easy?!  🙂

Thanks guys - it shows what formal training does for you (rather than learning as you go!)  🙂

I'm afraid I have to award the Correct Answer to the first reply.

Cheers,

Steve.

[its_anandrjs]

Hi,

Write a script some thing like this in your script

Table:

CrossTable(Months,Value,2)

Load * inline

[

Account, AccountName,          January,   February,     March

1,              My Account,          100,         200,             350

];

HTH

Regards,

Anand