topic Re: Repivot Excel Data in Load Script? in QlikView

Repivot Excel Data in Load Script?

Wed, 14 Nov 2012 18:51:37 GMT

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.

Re: Repivot Excel Data in Load Script?

Gysbert_Wassenaar — Wed, 14 Nov 2012 18:57:56 GMT

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

Re: Repivot Excel Data in Load Script?

MayilVahanan — Wed, 14 Nov 2012 18:58:56 GMT

Use

crosstable in script ..

Please refer this:

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

for example

Hope it helps

Re: Repivot Excel Data in Load Script?

Wed, 14 Nov 2012 19:00:45 GMT

Use "Crosstable" option.

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

Re: Repivot Excel Data in Load Script?

Wed, 14 Nov 2012 19:05:01 GMT

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.

Re: Repivot Excel Data in Load Script?

its_anandrjs — Wed, 14 Nov 2012 19:05:24 GMT

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